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

Question

I am looking for some docs and/or examples for the new JSON functions in PostgreSQL 9.2.

Specifically, given a series of JSON records:

[
  {name: "Toby", occupation: "Software Engineer"},
  {name: "Zaphod", occupation: "Galactic President"}
]

How would I write the SQL to find a record by name?

In vanilla SQL:

SELECT * from json_data WHERE "name" = "Toby"

The official dev manual is quite sparse:

• http://www.postgresql.org/docs/devel/static/datatype-json.html
• http://www.postgresql.org/docs/devel/static/functions-json.html

Update I

I've put together a gist detailing what is currently possible with PostgreSQL 9.2. Using some custom functions, it is possible to do things like:

SELECT id, json_string(data,'name') FROM things
WHERE json_string(data,'name') LIKE 'G%';

Update II

I've now moved my JSON functions into their own project:

PostSQL - a set of functions for transforming PostgreSQL and PL/v8 into a totally awesome JSON document store

Wherein

Just recently I found this blog post by Matt Schinckel, which explains in detail querying JSON in PostgreSQL schinckel.net/2014/05/25/querying-json-in-postgres – Myasthenia

@Myasthenia This post is actually about querying JSONB, which is quite distinct from JSON. My bad for not making that clearer in the post. – Titania

Answer 1

Postgres 9.2

I quote Andrew Dunstan on the pgsql-hackers list:

At some stage there will possibly be some json-processing (as opposed to 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 manual on new JSON functionality.
• The Postgres Wiki on new features in pg 9.3.

The answer to the original question in Postgres 9.3:

For a given table:

CREATE TABLE json_tbl (data json);

Query:

SELECT object
FROM   json_tbl
     , json_array_elements(data) AS object
WHERE  object->>'name' = 'Toby';

Advanced example:

• Query combinations with nested array of records in JSON datatype

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

• Index for finding an element in a JSON array

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 on json and jsonb data types and functions.
• The Postgres Wiki on JSONB in pg 9.4

The manual goes as far as suggesting:

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

Bold emphasis mine.
Also, 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.

• Major good news in the release notes of Postgres 9.5.

Functionality and performance has been improved with every major Postgres version since. It's pretty complete by now (as of Postgres 16). One major, notable addition in ...

Postgres 12

... is the SQL/JSON path language along with operators and functions. The answer to the example in the question can now be, for a given table (with jsonb):

CREATE TABLE jsonb_tbl (data jsonb);

SELECT jsonb_path_query_first(data, '$[*] ? (@.name == "Toby")') AS object
FROM   jsonb_tbl
WHERE  data @> '[{"name": "Toby"}]';  -- optional, for index support

Or equivalent:

...
WHERE  data @@ '$[*].name == "Toby"';

fiddle

See:

• Returning JSON arrary with particular property using Postgres

About indexing:

• Find rows containing a key in a JSONB array of records

Clear

Thanks, I've run into type issues really fast using the PLV8 approach. Looks promising, but not really usable at the moment. – Wherein

@TobyHede: Guess we'll have to wait for 9.3 then. – Clear

@JoeShaw: Thanks, I updated accordingly and added a link to the Postgres Wiki. – Clear

@ErwinBrandstetter if i'm looking for WHERE elem->>'correct' = 'TRUE'; and the JSON looks like this: "correct":"TRUE", what is the right way to query logical terms? – Kloster

@Shiraj: Please ask the new questions as question. Comments are not the place. – Clear

Answer 2

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

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

see http://clarkdave.net/2013/06/what-can-you-do-with-postgresql-and-json/ for some nice examples and a tutorial.

Simsar

In the example above you should have a field named data with a JSON document: {images:{thumbnail:{url:'thumbnail.jpg'}}}. Let us know what your data looks like and what query is failing. – Simsar

#22667901 – Setiform

How can you query if there is an array? I see the #>> operator but no clue to how to use it! – Setiform

In this select query can I use wildcard? I.e SELECT data->'%'->'thumbnail'->'url' AS thumb FROM instagram; – Osculum

@Meekohi's answer works well: specifically I did not need ::json as described in other posts. Also note the -> operator will throw an error if you try to access a property which does not exist (i.e. if you have staggered JSON): ERROR: column "jsonPropertyYouWant" does not exist – Jackleg

Answer 3

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

seed.rb

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

se.save

rails c

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

returns

                                 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;

return

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

Rowlandson