Postgresql Insert select with multiple rows

I am creating a procedure to parse input json data and stored in tables. The function looks like:

create or replace function test_func(d json)
returns void as $$
  begin
    with n as (
     insert into t1 (name) values (d::json -> 'name') returning id
    ), c as (
     insert into t2 (cars) values json_array_elements_text(d::json -> 'cars') returning id
    )
    insert into t3 (id, name_id, cars_id, brand)
    select 1, n.id, c.id, json_array_elements_text(d::json -> 'brands') from n, c;
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,
  "car_type" int
)

CREATE TABLE t3
(
  "id" int,
  "name_id" int REFERENCES t1(id),
  "cars_id" int REFERENCES t2(id),
  "brand" text
)

the data input has name as text, cars and brands are array, all wrapped in a json. So the last insert has mixed value types, if the person has two cars, i got 4 rows inserted into t3 because c.id and json_array_elements_text(d::json -> 'brands') both have two data sets, 2x2 = 4, how can I map the inserted value to one on one? so 1st c.id should be mapped to 1st brand.

To map them you have to join not on true, but on distinct row.

here is example how to join both on id with ordinality - hope it will help. based on your json sample

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 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
;
        car        | brand | id
-------------------+-------+----
 bmw X5 xdrive     | bmw   |  1
 volvo v90 rdesign | volvo |  2
(2 rows)

update elaborating for OP:

you can avoid mapping multiple rows, by aggregating em and then using index:

your fn:

create or replace function test_func(d json)
returns void as $$
  begin
with 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;

your tables:

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 );

execution:

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)