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.
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.
