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

Question

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"}}});

Wolph · Accepted Answer · 2013-11-27 11:46:46Z

4

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

answered Nov 27, 2013 at 11:46

Wolph

80.4k12 gold badges142 silver badges152 bronze badges

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

Comments

foibs · Accepted Answer · 2013-11-28 08:48:00Z

-3

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.

edited Nov 28, 2013 at 8:48

answered Nov 27, 2013 at 12:23

foibs

3,4141 gold badge21 silver badges13 bronze badges

6 Comments

Ihor Romanchenko Over a year ago

You can use inexes for JSON with latest versions of Postgres (but is less efficient than indexes on plain columns).

Muton Over a year ago

I accepted your answer although would challenge your view of PostgreSQL being limited to relational model. With the hstore data-type you can already use it for storing semi-structured documents.

Muton Over a year ago

But ok, it cannot be used as a document-store yet it seems, this is what I was evaluating here, but there's hope: sai.msu.su/~megera/postgres/talks/hstore-dublin-2013.pdf

foibs Over a year ago

@Muton: Thanks for accepting. Postgres is a relational database and doesn't claim to be anything else. Perhaps the steps being made with latest updates will allow a future support of both document-store and relational model support, but for now it is not a good tool for this job. For now, Postgres is great for storing and retrieving custom structures, but not good for searching in structured data. Until this changes (maybe in version 10, who knows), postgres cannot support high-end production services which utilize searching in structured data. These are the 2 cents of a big postgres fanboy

Craig Ringer Over a year ago

Please note that PostgreSQL 9.4 (or the next rev, if things go wrong) will include hstore v2 with support for nested/heirachical and typed storage. This is being used as the basis for v2 json support, so PostgreSQL will be able to index arbitrary json key trees (at the cost of big indexes). It might be worth mentioning that this is improving soon in your answer.

|

Collectives™ on Stack Overflow

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

2 Answers 2

Comments

6 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

6 Comments

Your Answer

Sign up or log in

Post as a guest

Related