Unnest elements from JSON array column

Question

I have a Postgres table test like this:

id | data
---+----------------------------------
0  | {'0':'a','1':'b','2':'c'}
1  | {'0':'d','1':'e' }
2  | {'0':'f','1':'g','2':'h','3':'i'}

How to get the following output?

id | data
---+-----
0  | a
0  | b
0  | c
1  | d
1  | e
2  | f
2  | g
2  | h
2  | i

Answer 1

Use json_each_text() in a LATERAL join:

SELECT t.id, d.data
FROM   test t, json_each_text(t.data) d(key, data)
ORDER  BY t.id, d.key;

db<>fiddle here

• What is the difference between LATERAL and a subquery in PostgreSQL?

However, what you display is not a JSON array, just a JSON object with key/value pairs, and with illegal syntax on top of it. Must be all double quotes ...