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.

Answer 1

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.