0

I want to choose which to extract and show it to each column, I want to have result like this query, but don't want to type this for every single thing like this

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

this show null, How to do if I choose to extract 'reach' and 'clicks',... to column but not all in the json?

select * 
from json_to_record('{"reach": 240, "spend": 3.34, "clicks": 10, "frequency": 1.0375}') 
as x(a int, b text, d text, e text)

I refer this Stack over flow question

My DEMO

EDIT: I have the main question is: how to choose which to extract without extract all like the 2nd query? The data have many rows, each row have json, can I do that with Json_to_record ?

Tom Tom
  • 328
  • 4
  • 15
  • You need `as x(reach int, spend numeric, clicks int, frequency numeric)` –  Oct 05 '20 at 12:12
  • I have the main question is: how to choose which to extract without extract all like the 2nd query? The data have many rows, each row have json, can I do that with `Json_to_record` ? @a_horse_with_no_name – Tom Tom Oct 06 '20 at 02:17
  • There is no way to do that without writing each column explicitly at one point or the other. –  Oct 06 '20 at 05:29
  • I think there is in my other question, " just specify the columns you want in the return structure" https://stackoverflow.com/questions/64219565/how-to-get-data-from-json-to-multiple-column-postgresql/64219994#64219994 – Tom Tom Oct 07 '20 at 03:35

2 Answers2

1

As a_horse_with_no_name said keep the same record names as these in the JSON and use aliases in the select list if needed.

select 
  reach as a, spend as b, clicks as c, frequency as d
from json_to_record('{"reach": 240, "spend": 3.34, "clicks": 10, "frequency": 1.0375}') 
as x(reach integer, spend numeric, clicks integer, frequency numeric);
Stefanov.sm
  • 11,215
  • 2
  • 21
  • 21
  • his issue is that the scalar values come as null. You dont have to write column name twice. – The Fool Oct 05 '20 at 13:50
  • No, they come just fine. – Stefanov.sm Oct 05 '20 at 13:55
  • no they don't! check his fiddle. Its all null where there is a text scalar value. Also you see the columns are named just fine. No need to alias with same name again. ![image](https://user-images.githubusercontent.com/39703898/95109580-b0276e00-0734-11eb-9279-f5f943c253a1.png) – The Fool Oct 05 '20 at 17:01
  • 1
    @TheFool: You have to "write column names twice" here to not get the `NULL`s. The [documentation](https://www.postgresql.org/docs/11/functions-json.html#FUNCTIONS-JSON-PROCESSING-TABLE): "The function(s) ... `json[b]_to_record` ... extract(s) the values associated with keys **whose names match column names of the output row type**. Object fields that do not correspond to any output column name are ignored, and **output columns that do not match any object field will be filled with nulls**.". If you want other names than the keys, the query this answer shows is *at least* one solution. – sticky bit Oct 05 '20 at 23:45
  • I have the main question is: how to choose which to extract without extract all like the 2nd query? The data have many rows, each row have json, can I do that with `Json_to_record` ? @Stefanov.sm – Tom Tom Oct 06 '20 at 02:19
  • I see, I got confused by the fiddle. Sorry about that. – The Fool Oct 06 '20 at 08:22
  • @HaoHao: Query the data table `cross join lateral` the query in my answer with a where clause. – Stefanov.sm Oct 06 '20 at 11:04
  • Please add DEMO @Stefanov.sm – Tom Tom Oct 07 '20 at 03:27
  • the answer here: https://stackoverflow.com/questions/64219565/how-to-get-data-from-json-to-multiple-column-postgresql/64219994#64219994 – Tom Tom Oct 07 '20 at 03:43
1

If you only want to select partial data from the JSON object, lets say 2 out of 4 keys, you can do so easily by omitting the rest of the keys from the anonymous table declaration. You need to use the JSON keys as column names.

select  *
from json_to_record('{"reach": 240, "spend": 3.34, "clicks": 10, "frequency": 1.0375}') 
as x(reach int,  clicks int)

This allows you to get the columns you need with little writing effort.

https://dbfiddle.uk/?rdbms=postgres_11&fiddle=dd39d912f6e696a8ace3670acf606959

The Fool
  • 16,715
  • 5
  • 52
  • 86
  • I ask another question but more concise and it get answer here: https://stackoverflow.com/questions/64219565/how-to-get-data-from-json-to-multiple-column-postgresql/64219994#64219994 – Tom Tom Oct 07 '20 at 03:42
  • Please discuss more in this question https://stackoverflow.com/questions/64219565/how-to-get-data-from-json-to-multiple-column-postgresql/64219994#64219994 – Tom Tom Oct 07 '20 at 03:45