Postgres Query JSON Array that contains something

问题:

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"

回答1:

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.