Querying JSON field in PostgreSQL

Question

I have a table with several fields (id, title, json_field). I want to select all rows from this table, but I want each parameter inside json_field to be a separate row in result. I guess I should use json_array_elements, but the problem is that I can't understand how to include it in my query.

Is there are solution? I expected it to work with with this query:

select id, title, json_array_elements(json_field) from table_name, but it doesn't.

I also tried to pass subquery result as an argument, not succeeded as well.

I read through the docs (http://www.postgresql.org/docs/9.3/static/functions-json.html) but I can't find anything helpful.

Thanks a lot for the help!

• Clodoaldo Neto: Show the json values
• Bulat: Do you get an error? or how exactly it does not work?
• Bulat: when you ask questions please provide table definitions, sample data and expected results. otherwise it is too much guesswork.

Answer 1

I have tried to apply json_array_elements() function to VARCHAR and TEXT fields and it does not work:

HINT: No function matches the given name and argument types. You might need to add explicit type casts.

However, it seems to work fine with JSON type. See SqlFiddle: http://sqlfiddle.com/#!15/2977f/3

So all you need to do is to cast your field to JSON data type: http://sqlfiddle.com/#!15/0fa19/1

• kpotehin: Thank you! That was a problem. I use Doctrine to communicate with database, and the field type was text, not json. I've just changed it to json and it works fine.

Answer 2

Works for me

select 1, 2, json_array_elements('[1,true, [2,false]]');
 ?column? | ?column? | json_array_elements 
----------+----------+---------------------
 1 | 2 | 1
 1 | 2 | true
 1 | 2 | [2,false]

Also works with a table

select id, title, json_array_elements(j) as j
from (values
 (1, 'the title', '[1,true, [2,false]]'::json)
) s(id, title, j);
 id | title | j 
----+-----------+-----------
 1 | the title | 1
 1 | the title | true
 1 | the title | [2,false]

• Bulat: how about the real table?
• Clodoaldo Neto: @Bulat I don't see a real table in the question. Do you?
• Bulat: I do: "I have a table with several fields (id, title, json_field)."
• Clodoaldo Neto: @Bulat He has. I don't. The best I can do it to create one myself as I did on my real answer.
• Bulat: Let us continue this discussion in chat.