How we can turn json list into rows in postgres?

Question

i have a query that returns a json list:

 select type.value -> 'components' as json_data
   from manifests m left join
        jsonb_array_elements(m.json_data->'type') as type on true

this returns the following data

[{"name": "xxx", "version": "1.1"}, {"name": "xxx2", "version": "1.2"}]

i can proceed it further if i use it as a text like:

 select *
   from jsonb_array_elements(
  '[{"name": "xxx", "version": "1.1"}, {"name": "xxx2", "version": "1.2"}]' )

gives

 {"name": "xxx", "version": "1.1"}
 {"name": "xxx2", "version": "1.2"}

but how i can reach the same using the first query? i cannot make it work using a subquery

update: my original data looks like this:

{
    "lastUpdated": 1569393969656,
    "type": [
        {
            "components": [
                {
                    "name": "xxx",
                    "version": "1.1"
                },
                {
                    "name": "xxx2",
                    "version": "1.2"
                }
            ],
            "description": "xxx"
        }
    ]
}

thanks

Barbaros Özhan · Accepted Answer · 2019-09-27 19:48:16Z

1

You can apply jsonb_array_elements() function twice :

select jsonb_array_elements(elm) as json_data 
  from
  (
   select jsonb_array_elements(json_data -> 'type') -> 'components' as elm 
     from manifests ) q

to be able to extract the related array elements in the subquery.

Demo

edited Sep 27, 2019 at 19:48

answered Sep 27, 2019 at 13:16

Barbaros Özhan

65.9k11 gold badges36 silver badges64 bronze badges

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

5 Comments

clairvoyant Over a year ago

Query execution failed Reason: SQL Error [22023]: ERROR: cannot extract elements from an object

clairvoyant Over a year ago

my original table does not hold data like that, that is already a result of a transformation (see first query)

clairvoyant Over a year ago

i need a solution which uses the first query

Barbaros Özhan Over a year ago

@clairvoyant can you share the original content of json_data directly?

clairvoyant Over a year ago

i changed parcel to type to avoid any confusion for others

Collectives™ on Stack Overflow

How we can turn json list into rows in postgres?

1 Answer 1

5 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

5 Comments

Your Answer

Sign up or log in

Post as a guest

Related