PostgreSQL retrieving a row for each item in a json array

Question

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;

I think this is somewhat similar to your problem, so you should take a look if not already done : stackoverflow.com/a/20282427/11260467 — Xiidref
– Xiidref, Commented Apr 5, 2022 at 15:57

Philippe · Accepted Answer · 2022-04-05 15:53:51Z

2

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;

answered Apr 5, 2022 at 15:53

Philippe

1,8376 silver badges19 bronze badges

Sign up to request clarification or add additional context in comments.

1 Answer 1