Skip to content
Advertisement

JSON array to a single row in SQL table

I have a table in PostgreSQL database.

With some data in it:

receipt column contains lists of 3-element lists.

What I’m trying to achieve is write an SQL query that returns table in the form

The best I’ve got so far is

but I’m stuck on unnesting the inner arrays. I tried using jsonb_to_record, but is seems to work with JSON objects, not arrays. I will appreciate your advice.

Advertisement

Answer

You were almost there, you only have to fetch each element from the array you extracted as you can see below

gives the result:

User contributions licensed under: CC BY-SA
9 People found this is helpful
Advertisement