Postgresql – How to populate a table from a json array column in another table?

Use json_populate_record() or json_populate_recordset().
Short, casts to target types implicitly, and fast.

But get your setup and terminology straight!

json[] is a Postgres array of json. While this is totally possible, it’s unlikely. Once you work with JSON, you would typically nest a JSON array in the plain Postgres type json.
Depending on what you have exactly, setup and solution are subtly different:

json[]: Postgres array of json

(Less likely.)

Setup

CREATE TABLE foos (
  id int
, baas json[]  -- type json[] - Postgres array of json (??)
);

INSERT INTO foos VALUES
  (1, '{"{"name": "a1", "property": "1"}"
       ,"{"name": "b1", "property": "2"}"}')    -- pg array with 2 json elements: JSON objects with two properites each
, (2, '{"[{"name": "a2", "property": "1"}
         ,{"name": "b2", "property": "2"}]"}')  -- pg array with 1 json element: JSON array with 2 objects each, two properites each
;

Wait, that syntax looks confusing! See:

• How to pass custom type array to Postgres function

Queries

One of these two:

INSERT INTO baas (foo_id, name, property)
SELECT f.id, b.name, b.property
FROM   foos f, unnest(f.baas) a(baas), json_populate_record(null::baas, a.baas) b
WHERE  f.id = 1;

INSERT INTO baas (foo_id, name, property)
SELECT f.id, b.name, b.property
FROM   foos f, unnest(f.baas) a(baas), json_populate_recordset(null::baas, a.baas) b
WHERE  f.id = 2;

json: scalar type with nested JSON array

(More likely.)

Setup

-- DROP TABLE IF EXISTS foos;
CREATE TEMP TABLE foos (
  id int
, baas json  -- type json (with nested JSON array)
);

INSERT INTO foos VALUES
  (3, '[{"name": "a", "property": "1"}
      , {"name": "b", "property": "2"}]')  -- 1 JSON array with 2 objects, each with 2 properties
;

Query

INSERT INTO baas (foo_id, name, property)
SELECT f.id, b.name, b.property
FROM   foos f, json_populate_recordset(null::baas, f.baas) b;

fiddle