CODEWITHSUNDEEP
jsonpostgresql
Muton
Muton

Reputation: 819

PostgreSQL: How to check if any of the elements in a JSON array match criteria

Assuming the following schema in PostgreSQL 9.3:

create table test (data json);
insert into test values ('{
  "id" : 1,
  "name" : "abc",
  "values" : [{"id" : 2, "name" : "cde"}, {"id" : 3, "name" : "def"}]
}'::json);
insert into test values ('{
  "id" : 4,
  "name" : "efg",
  "values" : [{"id" : 5, "name" : "fgh"}, {"id" : 6, "name" : "ghi"}]
}'::json);

What is the best way to query for documents where at least one of the objects in the "values" array satisfy a criteria? The only way I could come up with is this:

select data
from (select
        data, 
        json_array_elements(data->'values')->>'name' as valueName
      from test) a
where valueName = 'ghi';

Is there a way to do this without the nested query? In MongoDB I could simply say:

db.test.find({values : {$elemMatch : {name : "ghi"}}});

Upvotes: 3

Views: 3125

Answers (2)

foibs
foibs

Reputation: 3406

PostgreSQL is a object-relational database system, MongoDB is a NoSQL database, so there is no kind of similarity among them (except for the obvious part that they are both used to store data).

You cannot do your search without the nested query. You can create a postgres function or custom type (or both) to aid you, but still, internally a nested query is needed.

It is very important to understand that postgres structured data columns like json are not meant to be used this way. It is mostly a shortcut and a utility to convert data when inserting or selecting, so that your program that executes the sql queries will not need to do extra conversions.

However, you shouldn't be searching in the fields of structured data like this. It is extremely inefficient, you are putting a big load on your server for no reason and you can't use indexes as efficiently (corrected after Igor's comment). Your database will become unusable with a few thousand rows. (I could say more....). I strongly suggest you take the time rearrange your data in more columns and tables so you can easily select from them with the use of indexes and without nested queries. Then you can easily use to_json() to get your data in json format.

EDIT/NOTE:

This answer was written when the current version of Postgres was 9.3 and applies to 9.3 and prior. It is almost certain that Postgres will be able to completely support document store with fully indexed and efficient search in elements, in the (near) future. Each upgrade since 9.0 has been a step to that direction.

Upvotes: -2

Wolph
Wolph

Reputation: 80031

Well... you could do something like this if you prefer subqueries:

select value
from (
  select json_array_elements(data -> 'values')
  from test
) s(value)
where value ->> 'name' = 'ghi'

But beyond that there is no function available to do what you want. You could easily create your own operator or stored procedure to take care of this however.

Here's a fiddle btw: http://sqlfiddle.com/#!15/fb529/32

Upvotes: 3

Related Questions