CODEWITHSUNDEEP
sqljsonpostgresql
Tom Tom
Tom Tom

Reputation: 352

How to get data from Json to multiple column PostgreSQL

I want to extract many objects (not all object) in JSON but don't want to type for every single thing like this:

Select *,  
metrics::json ->> 'spend',
metrics::json ->> 'impressions',
metrics::json ->> 'clicks'
from t1

Here my DEMO show real data and case, how to extract to multiple columns and cast it to exact data type (float, integer)

I refer this question, can I use json_to_record for this or other method and how?

Upvotes: 0

Views: 584

Answers (1)

Blue Star
Blue Star

Reputation: 1952

You can just specify the columns you want in the return structure. So if you're only interested in "spend" and "impressions", you can do

SELECT x.*
FROM t1, json_to_record(t1.metrics) 
AS x(spend numeric, impressions int);

Upvotes: 1

Related Questions