要映射它们，您必须不是在true上加入，而是在不同的行上加入。
这是示例如何在id上同时加入两者
with ordinality-希望能有所帮助。根据您的json样本
t=# with j as (select '{"name":"john", "cars":["bmw X5 xdrive","volvo v90 rdesign"], "brands":["bmw","volvo"]}'::json d)select car,brand,t1.id from jjoin json_array_elements_text(j.d->'cars') with ordinality t1(car,id) on truejoin json_array_elements_text(j.d->'brands') with ordinality t2(brand,id) on t1.id = t2.id; car | brand | id-------------------+-------+---- bmw X5 xdrive | bmw | 1 volvo v90 rdesign | volvo | 2(2 rows)更新 OP的详细说明：
您可以通过汇总em然后使用index来避免映射多个行：
您的电话号码：
create or replace function test_func(d json)returns void as $$ beginwith j as (select d), a as ( select car,brand,t1.id oid from j join json_array_elements_text(j.d->'cars') with ordinality t1(car,id) on true join json_array_elements_text(j.d->'brands') with ordinality t2(brand,id) on t1.id = t2.id), n as ( insert into t1 (name) values (d::json -> 'name') returning id), c as ( insert into t2 (cars) select car from a order by oid returning id), ag as ( select array_agg(c.id) cid from c)insert into t3 (id, name_id, cars_id, brand) select 1, n.id,cid[oid], brand from a join n on true join ag on true;end;$$ language plpgsql;
表：
CREATE TABLE t1 ( "id" SERIAL PRIMARY KEY, "name" text NOT NULL );CREATE TABLE t2 ( "id" SERIAL PRIMARY KEY, "cars" text NOT NULL );CREATE TABLE t3 ( "id" int, "name_id" int REFERENCES t1(id), "cars_id" int REFERENCES t2(id), "brand" text );
执行：
t=# select test_func('{"name":"john", "cars":["bmw X5 xdrive","volvo v90 rdesign"], "brands":["bmw","volvo"]}'); test_func-----------(1 row)t=# select * from t1; id | name----+-------- 14 | "john"(1 row)t=# select * from t2; id | cars----+------------------- 27 | bmw X5 xdrive 28 | volvo v90 rdesign(2 rows)t=# select * from t3; id | name_id | cars_id | brand----+---------+---------+------- 1 | 14 | 27 | bmw 1 | 14 | 28 | volvo(2 rows)
