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

Question

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.

• Erwin Brandstetter: Please provide a proper example with valid syntax, your version of Postgres, the table definition and what you have tried - even if it's not working.
• Trasplazio Garzuglio: @ErwinBrandstetter sorry about that, you are right. Thing is I don't actually have anything ready, I'm evaluating switching from MongoDB to PostgreSQL and I need to make sure that I can translate a distinct query in MongoDB to PostgreSQL.
• blong: Here's an additional postgresql JSON question you might find interesting: dba.stackexchange.com/q/281480/45101

Answer 1

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:

• How to remove object from json array?
• How to turn JSON array into Postgres array?
• How to preserve the original order of elements in an unnested array?
• Select first row in each GROUP BY group?