0

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?

Tom Tom
  • 328
  • 4
  • 15

1 Answers1

1

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);
Blue Star
  • 1,932
  • 1
  • 10
  • 11
  • When we use Numeric? can we always use numeric for all numbers include `int`? @Blue Star – Tom Tom Oct 07 '20 at 03:45
  • 1
    I actually just used numeric because your example had spend as numeric. I would only recommend using numeric when you need exact precision of decimal numbers, like money (cents). – Blue Star Oct 07 '20 at 03:50
  • Hold on! in my real case, I want to extract `'unique_actions.link_click'` object then make the column name `link_click` but cannot do that this way , Do you have an idea? Plz do that in demo https://dbfiddle.uk/?rdbms=postgres_11&fiddle=0700bf64a7bbe082be05f69e7f944e25 @Blue Star – Tom Tom Oct 07 '20 at 04:06
  • 1
    If a JSON key has a period in the middle, you'll need to enclose it in double quotes for it to work. If you want to change its column name, you'll need to specify it explicitly in the select clause (`SELECT x.*, "unique_actions.link_click" as link_click`). – Blue Star Oct 07 '20 at 04:16
  • if so I need to type `unique_actions.link_click` twice (one for name , one for extract data). Do you think it's optimal? any other way? @blue star – Tom Tom Oct 07 '20 at 04:46
  • I'm not aware of any shorter way than the one you mentioned and the one in my answer. – Blue Star Oct 07 '20 at 04:47