How do I query using fields inside the new PostgreSQL JSON datatype? How do I query using fields inside the new PostgreSQL JSON datatype? json json

How do I query using fields inside the new PostgreSQL JSON datatype?

Postgres 9.2

I quote Andrew Dunstan on the pgsql-hackers list:

At some stage there will possibly be some json-processing (as opposedto json-producing) functions, but not in 9.2.

Doesn't prevent him from providing an example implementation in PLV8 that should solve your problem. (Link is dead now, see modern PLV8 instead.)

Postgres 9.3

Offers an arsenal of new functions and operators to add "json-processing".

The answer to the original question in Postgres 9.3:

SELECT *FROM   json_array_elements(  '[{"name": "Toby", "occupation": "Software Engineer"},    {"name": "Zaphod", "occupation": "Galactic President"} ]'  ) AS elemWHERE elem->>'name' = 'Toby';

Advanced example:

For bigger tables you may want to add an expression index to increase performance:

Postgres 9.4

Adds jsonb (b for "binary", values are stored as native Postgres types) and yet more functionality for both types. In addition to expression indexes mentioned above, jsonb also supports GIN, btree and hash indexes, GIN being the most potent of these.

The manual goes as far as suggesting:

In general, most applications should prefer to store JSON data asjsonb, unless there are quite specialized needs, such as legacyassumptions about ordering of object keys.

Bold emphasis mine.

Performance benefits from general improvements to GIN indexes.

Postgres 9.5

Complete jsonb functions and operators. Add more functions to manipulate jsonb in place and for display.

With Postgres 9.3+, just use the -> operator. For example,

SELECT data->'images'->'thumbnail'->'url' AS thumb FROM instagram;

see for some nice examples and a tutorial.

With postgres 9.3 use -> for object access. 4 example


se = = {    params:    [        {            type: 1,            code: 1,            value: 2012,            description: 'year of producction'        },        {            type: 1,            code: 2,            value: 30,            description: 'length'        }    ]}

rails c

SELECT data->'params'->0 as data FROM smart_elements;


                                 data---------------------------------------------------------------------- {"type":1,"code":1,"value":2012,"description":"year of producction"}(1 row)

You can continue nesting

SELECT data->'params'->0->'type' as data FROM smart_elements;


 data------ 1(1 row)