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.
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)
SELECT string_agg(x->>'Model', ',')
FROM cars
CROSS JOIN LATERAL
jsonb_array_elements(data) x
GROUP BY cars;
┌────────────┐
│ string_agg │
├────────────┤
│ Golf,M3 │
└────────────┘
(1 row)
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!