6

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"
Axil
  • 3,606
  • 10
  • 62
  • 136
  • Use `json_array_each` in a `LATERAL` query. – Craig Ringer Mar 09 '14 at 08: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 09 '14 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 '14 at 00:06
  • I guess you meant "James" and **"Dickens"** (not "Tom"). Right? – MatheusOl Mar 10 '14 at 01:18
  • sorry my mistake, you're right – Axil Mar 10 '14 at 02:15

1 Answers1

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.

MatheusOl
  • 10,870
  • 3
  • 30
  • 28