How to get a json object as column in postgresql?

Question

I have these table on mu PostgreSQL 9.05:

Table: core Fields: name, description, data

data field is a json field, with (for example): {"id": "100", "tax": "4,5"}

Always is one json per data.

My question is: can I get all JSON fields as query fields? return like these: name, description, id, tax....

The problem is: my JSON does have various fields, can be Id, tax or other.

See [Postgres: Flatten aggregated key/value pairs from a JSONB field?](http://stackoverflow.com/a/35179515/1995738) — klin, Oct 09 '16 at 15:36

score 32 · Answer 1 · edited Nov 04 '19 at 10:01

You can't do that "dynamically". You need to specify the columns you want to have:

select name, description, id, 
       data ->> 'tax' as tax,
       data ->> 'other_attribute' as other_attribute
from core;

If you do that a lot, you might want to put that into a view.

Another option is to create an object type in Postgres that represents the attributes in your JSON, e.g.

create type core_type as (id integer, tax numeric, price numeric, code varchar);

You can then cast the JSON to that type and the corresponding attributes from the JSON will automatically be converted to columns:

With the above type and the following JSON: {"id": "100", "tax": "4.5", "price": "10", "code": "YXCV"} you can do:

select id, (json_populate_record(null::core_type, data)).*
from core;

and it will return:

id | tax  | price | code
---+------+-------+-----
 1 | 4.50 |    10 | YXCV

But you need to make sure that every JSON value can be cast to the type of the corresponding object field.

If you change the object type, any query using it will automatically be updated. So you can manage the columns you are interested in, through a central definition.

Thanks, but I don't know all columns can be inside json, can be 1 or 2.. or 10... can I do that, dynamically? — fh_bash, Oct 09 '16 at 18:10
@fh_bash: as I said: you can't do that "dynamically". In SQL the number of columns of a query must be defined _before_ that query runs, and all rows of a result must have the same number of columns. — , Oct 09 '16 at 18:45

score 23 · Answer 2 · edited Aug 06 '18 at 12:01

As of PostgreSQL 9.4 you can also use json_to_record.

Builds an arbitrary record from a JSON object (see note below). As with all functions returning record, the caller must explicitly define the structure of the record with an AS clause.

For example:

select * from json_to_record('{"a":1,"b":[1,2,3],"c":"bar"}') as x(a int, b text, d text)

Returns

 a |    b    | d
---+---------+---
 1 | [1,2,3] |

score 1 · Answer 3 · answered Jun 25 '21 at 17:16

I find it quite irksome that no one has identified an appropriate solution.

Reading the OP question as: "given a table which has a JSON field, having varying keys, and without foreknowledge of those keys' presence or identity, how would I obtain those values as part of a unified recordset?"

A solution ideally would accomplish this in a single script, being useful as a view or source for an application, but the following is useful in a stored procedure, or else if your JSON schema rarely changes.

select
    concat_ws(
        ' ',
        e'select\n\t*,',
        string_agg(
            distinct concat(
                e'\tcase ',e'json_field?\'', json_column_key, e'\'\n\t\t',
                e'when true then json_field->\'', json_column_key,e'\'\n\t\t',
                e'else NULL\n\t',
                'end as ',json_column_key,
            ),
             e',\n\t'
        ),
        e'\nfrom table_with_json'
    ) as generated_sql_script
from (
    select json_object_keys(annotations) as json_column_key
    from table_with_json.answers
    where json_typeof(json_field) like 'object'
) as generate_promoted_json

The output is a recordset describing all columns in the parent table, including the column with the json object, but would have individual columns per property which may or may not be present on a specific record.

A few shortcomings:

The above does not specify a type for the data broken out. This could be an issue, and it might be worth it to modify the script to reflect that.
This does not deal with 2nd-level objects and arrays. If those are present in your schema, and if they vary, it could be that this script needs to be expanded to include that logic, possibly relegating it to a series of functions to be executed from the hypothetical stored procedure that prepares the generated query.

Those aside, it should be a general-purpose utility to hang onto.

How to get a json object as column in postgresql?

3 Answers3

Linked