Query For Array Elements Inside Json Type

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

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

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;

PostgreSQL: Query elements in a JSON array

demo:db<>fiddle

You can expand your array elements into one element each row with jsonb_array_elements(). This can be filtered:

SELECT
    id,
    elems.value
FROM
    mytable,
    jsonb_array_elements(data) elems
WHERE
    elems.value = '{"a":1}'

Postgres - query json with nested arrray and objects inside array

WITH data(content) AS ( VALUES
  ('{
    "id": 1,
         "external_order_id": {
        "id": "2"
    },
    "customer": {

        "external_customer_id": {
            "id": "3"
        }
    },
    "line_items": [
        {
            "sku": "SKU-1",
            "properties": [
                {
                    "name": "colour",
                    "value": "red"
                },
                {
                    "name": "size",
                    "value": "large"
                }
            ],
            "external_product_id": {
                "id": "4"
            },
            "external_variant_id": {
                "id": "5"
            }
        },
                {
            "sku": "SKU-2",
            "properties": [
                {
                    "name": "colour",
                    "value": "black"
                },
                {
                    "name": "size",
                    "value": "small"
                }
            ],
            "external_product_id": {
                "id": "8"
            },
            "external_variant_id": {
                "id": "9"
            }
        }
    ]

}'::jsonb)
)
select ord.*
,ext.id as external_order_id
,cus.id as external_customer_id
,line_items.sku
,line_items.external_product_id->>'id' as external_product_id
,line_items.external_variant_id->>'id' as external_variant_id
,props.*
FROM data,
jsonb_to_record(content) as ord(id int),
LATERAL jsonb_to_record(content->'external_order_id') as ext(id text),
LATERAL jsonb_to_record(content#>'{customer, external_customer_id}') as cus(id text)
CROSS JOIN LATERAL jsonb_to_recordset(content->'line_items') line_items(sku text, properties jsonb, external_product_id jsonb, external_variant_id jsonb)
cross join LATERAL jsonb_to_recordset(line_items.properties) props(name text, value text)

Get value from JSON array in postgres

Basically, you should use jsonb_array_elements() twice, for the main array and for its filtered element (which is an array too).

select value::numeric as result
from (
    select elem
    from the_data
    cross join jsonb_array_elements(col) as main(elem)
    where elem ? 'id'
    ) s
cross join jsonb_array_elements(elem)
where jsonb_typeof(value) = 'number'

Try it in Db<>Fiddle.

However, if you want to get exactly the third value from the nested array, the query may be simpler (note that array elements are indexing from 0):

select (elem->2)::numeric as result
from the_data
cross join jsonb_array_elements(col) as main(elem)
where elem ? 'id'

Db<>Fiddle.

---

Related Topics