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!

when you ask questions please provide table definitions, sample data and expected results. otherwise it is too much guesswork. — Bulat
– Bulat, Commented Sep 1, 2014 at 12:39

Bulat · Accepted Answer · 2014-09-01 12:30:44Z

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

answered Sep 1, 2014 at 12:30

Bulat

6,9992 gold badges32 silver badges54 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

kpotehin Over a year ago

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.

Clodoaldo Neto · Accepted Answer · 2014-09-01 12:28:45Z

1

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]

edited Sep 1, 2014 at 12:28

answered Sep 1, 2014 at 12:17

Clodoaldo Neto

127k30 gold badges251 silver badges274 bronze badges

5 Comments

Bulat Over a year ago

how about the real table?

Clodoaldo Neto Over a year ago

@Bulat I don't see a real table in the question. Do you?

Bulat Over a year ago

I do: "I have a table with several fields (id, title, json_field)."

Clodoaldo Neto Over a year ago

@Bulat He has. I don't. The best I can do it to create one myself as I did on my real answer.

Bulat Over a year ago

Let us continue this discussion in chat.

Collectives™ on Stack Overflow

Querying JSON field in PostgreSQL

2 Answers 2

1 Comment

5 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

1 Comment

5 Comments

Your Answer

Sign up or log in

Post as a guest

Related