CODEWITHSUNDEEP
sqljsonpostgresqljsonb
clairvoyant
clairvoyant

Reputation: 205

How we can turn json list into rows in postgres?

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

Upvotes: 1

Views: 140

Answers (1)

Barbaros Özhan
Barbaros Özhan

Reputation: 65228

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

Upvotes: 1

Related Questions