CODEWITHSUNDEEP
sqlpostgresql
TrsBar
TrsBar

Reputation: 139

PostgreSQL get json as record

I would like to be able to get a json object as a record.

SELECT select row_number() OVER () AS gid, feature->'properties' FROM dataset

The output of this query looks like this:

gid ?column? json
1 {"a": "1", "b":"2", "c": "3"}
2 {"a": "3", "b":"2", "c": "1"}
3 {"a": "1"}

The desired result :

gid a b c
1 1 2 3
2 3 2 1
3 1 null null

I can't use json_to_record because i don't know the number of fields. However, all my fields are text.

Upvotes: 1

Views: 100

Answers (1)

Laurenz Albe
Laurenz Albe

Reputation: 246083

There is no generic way to do that, because the number, type and name of columns have to be known at query parse time. So you would have to do:

SELECT row_number() OVER () AS gid,
       CAST(feature #>> '{properties,a}' AS integer) AS a,
       CAST(feature #>> '{properties,b}' AS integer) AS b,
       CAST(feature #>> '{properties,c}' AS integer) AS c
FROM dataset;

Essentially, you have to know the columns ahead of time and hard code them in the query.

Upvotes: 1

Related Questions