Postgres Query JSON Array that contains something

Question

Postgres has this JSON datatype and I am wondering how can I query data inside a JSON array ? I am using Postgres 9.3.1

I have inserted into PUBLISHER table that has these 2 fields name: string and data: json

INSERT INTO PUBLISHER (name, data) VALUES ('PUB1', [{"code":"PA1","author":"James","type":"Novel"},{"code":"PA2","author":"John","type":"Science"}] 

INSERT INTO PUBLISHER (name, data) VALUES ('PUB2', [{"code":"PA1","author":"Dickens","type":"Novel"},{"code":"PA2","author":"Tom","type":"Comic"}]

I want to do a query and list out authors with the type "Novel". In this case it would be "James" and "Tom" that should be the output.

Something of this sort of query:

select name, authorfromdata from publisher where data->type is "Novel"

Unless you have a VERY good reason, you should normalize and make an author table. Doing it this way will likely become a slow spaghetti monster. — Björn Nilsson
– Björn Nilsson, Commented Mar 9, 2014 at 18:51
Bjorn: agreed, its not the best way but there are some cases where i want to do a query on a certain type. typically i would just parse this json directly and not do a query. Craig: how do i do LATERAL and json_array_each, wondering if there is an example to it ? — Axil
– Axil, Commented Mar 10, 2014 at 0:06
I guess you meant "James" and "Dickens" (not "Tom"). Right? — MatheusOl
– MatheusOl, Commented Mar 10, 2014 at 1:18

MatheusOl · Accepted Answer · 2014-03-10 01:23:09Z

6

You can use the json_array_elements function to generate a SETOF json from an array:

SELECT name, json_array_elements(data) AS author
FROM publisher

Having that, you can use it as a subquery, so you can filter what you want, e.g.:

SELECT DISTINCT author->>'author'
FROM (
    SELECT name, json_array_elements(data) AS author
    FROM publisher
) t
WHERE t.author->>'type' = 'Novel';

Just note that if you have many rows in this table, the performance of such queries (at least for current version, 9.3) will be really bad. I'd recommend you to normalize the data into tables.

answered Mar 10, 2014 at 1:23

MatheusOl

12k3 gold badges33 silver badges29 bronze badges

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

Collectives™ on Stack Overflow

Postgres Query JSON Array that contains something

1 Answer 1

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related