Select rows where a value is found in json array

Question

I have a table uploads_table:

upload_id	value
A	[{"doc_name": "doc1a", "doc_type": "pdf"}, {"doc_name": "doc1b", "doc_type": "csv"}]
B	[{"doc_name": "doc2a", "doc_type": "csv"}, {"doc_name": "doc2b", "doc_type": "csv"}]
C	[{"doc_name": "doc3a", "doc_type": "pdf"}]

What would be the Postgres query to return all the upload_id, value from uploads_table which has at least one "doc_type" as "pdf".

Expected result:

upload_id	value
A	[{"doc_name": "doc1a", "doc_type": "pdf"}, {"doc_name": "doc1b", "doc_type": "csv"}]
C	[{"doc_name": "doc3a", "doc_type": "pdf"}]

Answer 1

Use the jsonb "contains" operator @>:

SELECT *
FROM   uploads_table
WHERE  value @> jsonb '[{"doc_type":"pdf"}]';

This is, of course, assuming that value is type jsonb - as it should be.

A GIN index on (value) will make this fast.
Even faster with a more specialized jsonb_path_ops index:

CREATE INDEX uploads_table_values_gin_idx ON uploads_table USING gin (value jsonb_path_ops);

See:

• What's the proper index for querying structures in arrays in Postgres jsonb?

Related:

• Query for key in an element of a JSON array
• SELECT by json array value

Answer 2

You can use the built-in function jsonb_array_elements in PostgreSQL. The query statement will be as follows:

SELECT *
FROM uploads_table
WHERE EXISTS (
    SELECT 1
    FROM jsonb_array_elements(value) AS doc
    WHERE doc->>'doc_type' = 'pdf'
);

The result is as shown in the image below: