CODEWITHSUNDEEP
sqljsonpostgresql
Olivia
Olivia

Reputation: 814

Selecting data within JSON

I'd like to pull data using SQL in Postgres but would like to go into the JSON result and only extract the data I need.

If I write (in Valentina studio):

Select "data" from "cars"

The first row looks like:

[{"Model": "Golf", "Make": "VW", "Engine": "2.9"}, 
{"Model": "M3", "Make": "BMW", "Engine": "3.0"}]

What I would like is simply:

Golf, M3 or "Golf", "M3"

Then I could also use the same method for "Make" or "Engine"

Essentially I don't want my results in JSON.

Upvotes: 2

Views: 65

Answers (3)

Olivia
Olivia

Reputation: 814

I think I also got what I wanted (and melted it the way I was planning to do) with:

    select obj->'Model' as model, obj->'Make' as make, 
obj->'Engine' as engine from data n, jsonb_array_elements(n.DATA) as obj

from the above I can also use:

select 
    elem->>'Model' as model,
    elem->>'Make' as make ,
    elem->>'Engine' as engine
from cars,
lateral jsonb_array_elements(data) elem;

Thanks for the help!

Upvotes: 0

Laurenz Albe
Laurenz Albe

Reputation: 246238

SELECT string_agg(x->>'Model', ',')
FROM cars
   CROSS JOIN LATERAL
      jsonb_array_elements(data) x
GROUP BY cars;

┌────────────┐
│ string_agg │
├────────────┤
│ Golf,M3    │
└────────────┘
(1 row)

Upvotes: 2

klin
klin

Reputation: 121534

Use json_array_elements():

with cars(data) as (
values
    ('[
        {"Model": "Golf", "Make": "VW", "Engine": "2.9"}, 
        {"Model": "M3", "Make": "BMW", "Engine": "3.0"}
    ]'::json)
)

select 
    elem->>'Model' as model,
    elem->>'Make' as make,
    elem->>'Engine' as engine
from cars,
lateral json_array_elements(data) elem

 model | make | engine 
-------+------+--------
 Golf  | VW   | 2.9
 M3    | BMW  | 3.0
(2 rows)

Upvotes: 2

Related Questions