PostgreSQL retrieving a row for each item in a json array-CodePudding

I have two tables, table_with_json which has id and text column in which a valid json is stored that has structure like:

{
"workers":
 [
  {
  "name":"Anne",
  "email":"[email protected]"
  },
  {
  "name":"Tom",
  "email":"[email protected]"
  }
 ]
}

and emails table which has two columns: table_with_json_id (a foreign key to table_with_json) and email

I need to fill up the emails table but I'm having trouble creating more than one row for each row in table_with_json

the result I want for above example would be inserting two rows to emails table

1 | id1 | [email protected]
2 | id1 | [email protected]

So far I've managed to get one row for every entry in table_with_json with this SQL:

INSERT INTO emails (table_with_json_id, email) 
SELECT id, text::json->'workers'->0->'email' 
FROM table_with_json;

CodePudding user response：

You can try something like this (result here)

INSERT INTO emails (table_with_json_id, email) 
SELECT id, json_array_elements(text::json->'workers')->>'email'
FROM table_with_json;