Query for Element of Array in JSON Column

Query for element of array in JSON column

For Postgres 9.4+ see adamc's later answer. Or:

• Query for array elements inside JSON type

Original answer for Postgres 9.3

Yes, that's possible:

SELECT *
FROM   tbl t, json_array_elements(t.json_col->'emails') AS elem
WHERE  elem->>'id' = 123;

tbl being your table name, json_col the name of the JSON column.

See also:

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

About the implicit CROSS JOIN LATERAL:

• PostgreSQL unnest() with element number

Index to support this kind of query:

• Index for finding an element in a JSON array

Check if an element is contained in the values ​(array) of a json column in MySql

This should do it:

SELECT name, data 
FROM articolo 
WHERE JSON_CONTAINS(data, '"8"', '$.fornitori')

The double quotes around 8 are important, in order to properly match the JSON data. On the other hand, the query consistently uses single quotes for string literals.

Query for array elements inside JSON type

jsonb in Postgres 9.4+

You can use the same query as below, just with jsonb_array_elements().

But rather use the jsonb "contains" operator @> in combination with a matching GIN index on the expression data->'objects':

CREATE INDEX reports_data_gin_idx ON reports
USING gin ((data->'objects') jsonb_path_ops);

SELECT * FROM reports WHERE data->'objects' @> '[{"src":"foo.png"}]';

Since the key objects holds a JSON array, we need to match the structure in the search term and wrap the array element into square brackets, too. Drop the array brackets when searching a plain record.

More explanation and options:

• Index for finding an element in a JSON array

json in Postgres 9.3+

Unnest the JSON array with the function json_array_elements() in a lateral join in the FROM clause and test for its elements:

SELECT data::text, obj
FROM   reports r, json_array_elements(r.data#>'{objects}') obj
WHERE  obj->>'src' = 'foo.png';

db<>fiddle here

_{Old sqlfiddle}

Or, equivalent for just a single level of nesting:

SELECT *
FROM   reports r, json_array_elements(r.data->'objects') obj
WHERE  obj->>'src' = 'foo.png';

->>, -> and #> operators are explained in the manual.

Both queries use an implicit JOIN LATERAL.

Closely related:

• Query for element of array in JSON column

SQL find elements is in JSON array column

You need an OPENJSON() call to parse the stored JSON array. The result is a table with columns key, value and type and in the value column is each element from the parsed JSON array. The column data type is nvarchar(max) with the same collation as tags column collation.

SELECT *
FROM (VALUES
   (1, '["test01"]'),
   (2, '["test02","test03"]')
) table_tags (id, tags)
WHERE EXISTS (
   SELECT 1 FROM OPENJSON(tags) WHERE [value] IN ('test01', 'test02')
)

Query for array elements inside JSON[] field array type

Your problem stems from the incorrect use of the type json[]. A json array is a single json object and its type is json, not json[]. Example:

create table test (id int, crew json);
insert into test values
(1, '
[
    {
        "workHours": "9",
        "workers": "50",
        "checker_rate": 100,
        "rate": 150,
        "name": "Ramona",
        "last": null,
        "boxRate": 2,
        "checkTraining": false,
        "editing": true,
        "ix": 0,
        "breakPay": 3.0833333333333335,
        "trainingPay": 0
    },
    {
        "workHours": "4",
        "workers": "50",
        "checker_rate": 120,
        "rate": 160,
        "name": "Ramon",
        "last": "Rosas",
        "boxRate": 2,
        "checkTraining": false,
        "editing": false,
        "id": 1,
        "breakPay": 1.5416666666666667,
        "trainingPay": 0
    }
]');

The function json_array_elements() works as expected:

select id, elem->'name' as name
from test
cross join json_array_elements(crew) elem;

 id |   name   
----+----------
  1 | "Ramona"
  1 | "Ramon"
(2 rows)

---

One of the queries (or both) should work well with json[]:

select id, elem->'name' as name
from test
cross join json_array_elements(crew[1]) elem;

select id, elem->'name' as name
from test
cross join unnest(crew)
cross join json_array_elements(unnest) elem;

How to query array in Postgres json column?

Updated for change column type from text[] to json

If your column type is JSON you can use two scenarios:

Demo

1. convert to jsonb and use ? operator (Postgres document)

select * from test where content_type::jsonb ? 'c';

2. Use json_array_elements_text

select distinct on(t.id) t.*
from 
  test t
  cross join json_array_elements_text(content_type) je
where
  je.value = 'c';

---

Old scenario

You can use any function to check values exist in an array or according to Postgres document use array operator @>

Demo

1. With any

select * from test where 'c' = any(content_type);

2. With @>

select * from test where content_type @> array['c'];

Query of Postgresql JSON column containing array of objects returns nulls

You need first expands a JSON array to a set of JSON elements (you can use: json_array_elements(json)):

SELECT 
      data -> 'dateTime' AS datetime,
      data -> 'value' ->> 'bpm' AS bpm,
      data -> 'value' ->> 'confidence' AS confidence 
FROM (SELECT json_array_elements(data) AS data FROM heart_rate_json) AS sq;

Select rows where json array contains specific element

select *
from   orders
where  json_exists(products, '$[*]?(@ == 8)')
;

https://docs.oracle.com/en/database/oracle/oracle-database/12.2/adjsn/condition-JSON_EXISTS.html#GUID-D60A7E52-8819-4D33-AEDB-223AB7BDE60A

---

Related Topics