Query JSON nested array and expand to multiple rows in Postgres table

Question

This is the JSON object I'm querying:

const data =
{"fruit":["apple"],"vegetables":["carrot","turnip"],"dairy":["milk","cheese"]}'

Here's what I want to return in my postgres table:

  category   | item
--------------------
  fruit      |  apple 
  vegetables |  carrot
  vegetables |  apple 
  dairy      |  milk
  dairy      |  cheese

This is what I've managed to do so far:

SELECT key as category, value as item
FROM json_each_text('${data}')

  category   | item
--------------------
  fruit      |  ["apple"] 
  vegetables |  ["carrot", "turnip"]
  dairy      |  ["milk", "cheese"]

Does anybody know how to unnest/expand the values in the item column onto new rows? Thanks :)

Answer 1

You were very close.

Just extract the items from the json arrays using json_array_elements_text:

SELECT key as category, json_array_elements_text(value::json) as item
FROM json_each_text('{"fruit":["apple"],"vegetables":["carrot","turnip"],"dairy":["milk","cheese"]}'::json);
  category  |  item  
------------+--------
 fruit      | apple
 vegetables | carrot
 vegetables | turnip
 dairy      | milk
 dairy      | cheese
(5 Zeilen)

In case you ever face this issue with other types of arrays, consider using UNNEST:

SELECT UNNEST(ARRAY['foo','bar']);
 unnest 
--------
 foo
 bar
(2 Zeilen)

SELECT UNNEST('{"foo","bar"}'::TEXT[]);
 unnest 
--------
 foo
 bar
(2 Zeilen)