Question 170077 in PostgreSQL Question posted in
The official documentation can be found here.

“Can Postgresql select rows where a value is found in a json array?”

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"}]
Tags:

2

Answers


  1. 0

    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:
    enter image description here

    Login or Signup to reply.
  2. 0

    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:

    Related:

    Login or Signup to reply.
Please signup or login to give your own answer.

Back To Top
Search