Postgresql – Is it possible to select distinct values in a json document using PostgreSQL

greatest-n-per-groupjsonpostgresql

I have a column that is using the JSON type. I would like to perform a query to select all distinct records for a particular field in the JSON string: I.e. given these three documents

{
  id: 1,
  s: "foo"
},
{
  id:2,
  s: "bar"
},
{
  id:3,
  s: "foo"
},

the query must check the "s" key for distinct values and return the documents with id 1 and 2.

Best Answer

Assuming a JSON array in a Postgres 9.4 jsonb column, this would do the job:

SELECT DISTINCT ON (doc->'s') doc
FROM  (
   SELECT '[
    {
      "id":1,
      "s":"foo"
    },
    {
      "id":2,
      "s":"bar"
    },
    {
      "id":3,
      "s":"foo"
    }]'::jsonb AS j
   ) t
   , jsonb_array_elements(t.j) WITH ORDINALITY t1(doc, rn)
ORDER  BY doc->'s', rn;

Or, unless s is a nested object, it's probably cheaper to fold on the text value instead of the jsonb (sub-)record. Just use the operator ->> instead of -> in this case. The result is the same:

 doc
----------------------
'{"s": "bar", "id": 2}'
'{"s": "foo", "id": 1}'

Replace the subquery t with your actual table.

Key elements are jsonb_array_elements() (or json_array_elements()) in a LATERAL join with WITH ORDINALITY and then the Postgres-specific DISTINCT ON.

Related, with more explanation:

Related Solutions

PostgreSQL JSON field performance with large documents

In PostgreSQL 9.3, performance will be fairly poor for large json documents. The whole document must be parsed in order to access a field, index an array, etc. It's stored on disk as a simple text representation, the json text.

PostgreSQL 9.4 is likely to change this, with support for jsonb storage on disk. It'll still have to read the whole json object from disk to access any part of it, but it won't have the same kinds of parsing overheads you'll see in 9.3. It'll also gain many of the indexing features of hstore.

In 9.3, I strongly suspect that hstore, which is stored in a compact binary representation on disk and supports a variety of indexing features, is much more appropriate for your needs.

Postgresql – find all JSON scalars in PostgreSQL 9.3

Horrible as it is, you could write a PL/PgSQL function that attempts to extract an element from the object, traps the exception if it fails, and returns true on success or false on exception.

Untested:

create or replace function json_isobject(obj json)
returns boolean immutable language plpgsql as $$
begin
    begin
        perform obj->'';
    exception
      when invalid_parameter_value then
        return false;
      when others then
        raise;
    end;
    return true;
end;
$$;

This'll be pretty inefficient, since it's creating a sub-transaction for each call.

Or you could just update to 9.4.

On 9.4 I can't reproduce your reported error. Trying to look up a key in a json scalar returns null.

test=> select ('{"k":"v"}'::json) -> 'k' -> 'blah';
 ?column? 
----------

(1 row)

... and the same with using ->> to dereference a scalar.

So I'm not sure if this function will work on 9.3, and it won't on 9.4. To be 9.4-compatible you'd have to test if current_setting('pg_version_num') >= 90400 and return a result based on json_typeof instead.

You might need to adapt it to use the same expression you use to trigger your reported error.

Best Answer

Related Solutions

PostgreSQL JSON field performance with large documents

Postgresql – find all JSON scalars in PostgreSQL 9.3

Related Question