current community

your communities

more stack exchange communities

Stack Exchange
tour help
careers 2.0
Take the 2-minute tour ×
Stack Overflow is a question and answer site for professional and enthusiast programmers. It's 100% free, no registration required.
up vote 1 down vote favorite

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"
share|improve this question
    
Use json_array_each in a LATERAL query. –  Craig Ringer Mar 9 at 8:28
    
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 Mar 9 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 Mar 10 at 0:06
    
I guess you meant "James" and "Dickens" (not "Tom"). Right? –  MatheusOl Mar 10 at 1:18
    
sorry my mistake, you're right –  Axil Mar 10 at 2:15

1 Answer 1

up vote 2 down vote accepted

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.

share|improve this answer

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.