Query JSONb Column Containing Array of JSON Objects

Query jsonb column containing array of JSON objects

Assuming this table definition:

CREATE TABLE segments (segments_id serial PRIMARY KEY, payload jsonb);

With JSON values like this:

INSERT INTO segments (payload)
VALUES ('[
            {
                "kind": "person",
                "limit": "1",
                "filter_term": "56",
                "selected_attr": "customer",
                "selected_operator": "less_than"
            },
            {
                "kind": "email",
                "filter_term": "marketer",
                "selected_attr": "job_title",
                "selected_operator": "equals"
            }
        ]'
   );

You want to return elements of a JSON array that contain the key/value pair "kind":"person" (not a nested JSON object {"kind":"person"}) - and count array elements as well as table rows (there may be multiple matching array elements per row).

Solutions

To get the count of rows containing a qualifying jsonb value in column segments:

SELECT count(*)
FROM   segments s
WHERE  s.payload @> '[{"kind":"person"}]';

To get all qualifying JSON array elements (being JSON objects themselves) - plus the total count of elements (may be greater than above count at the same time:

SELECT j.*
FROM   segments s
JOIN   LATERAL jsonb_array_elements(s.payload) j(elem) ON j.elem @> '{"kind":"person"}'
WHERE  s.payload @> '[{"kind":"person"}]';

Returns:


elem
------------------------------------------------------------
{"kind": "person", "limit": "1", "filter_term": "56", ... }

To get all at once:

SELECT j.*, count(*) OVER () AS ct_elem, s.ct_rows
FROM  (
   SELECT payload, count(*) OVER () AS ct_rows
   FROM   segments
   WHERE  payload @> '[{"kind":"person"}]'
   ) s
JOIN   LATERAL jsonb_array_elements(s.payload) j(elem) ON j.elem @> '{"kind":"person"}';

Returns (for a table with more entries):


elem                      | ct_elem | ct_rows
--------------------------+---------+---------
{"kind": "person",  ... } | 4       | 3
{"kind": "person",  ... } | 4       | 3
...

But I think you really want this:

SELECT a.*
     , sum(ct_elem_row) OVER () AS ct_elem_total
     , count(*)         OVER () AS ct_rows
FROM   segments s
JOIN   LATERAL (
   SELECT json_agg(j.elem) AS filtered_payload, count(*) AS ct_elem_row
   FROM   jsonb_array_elements(s.payload) j(elem)
   WHERE  j.elem @> '{"kind":"person"}'
   ) a ON ct_elem_row > 0
WHERE  s.payload @> '[{"kind":"person"}]';

Returns (for a table with more entries):


filtered_payload                                     | ct_elem_row | ct_elem_total | ct_rows
-----------------------------------------------------+-------------+---------------+---------
[{"kind": "person", ... }]                           | 1           | 4             | 3
[{"kind": "person", ... }]                           | 1           | 4             | 3
[{"kind": "person", ... }, {"kind": "person", ... }] | 2           | 4             | 3

This identifies matching rows, then select matching array elements and builds an array per row with only those. Plus counts.

For best performance you would have a jsonb_path_ops GIN index like:

CREATE INDEX segments_path_ops_gin_idx ON segments 
USING  gin (payload jsonb_path_ops);

(But a more generic index to serve more different queries may be a better choice.)

Index for finding an element in a JSON array
Query for array elements inside JSON type
Best way to get result count before LIMIT was applied

Terminology

We are dealing with a JSON object containing a JSON array, saved as Postgres jsonb data type - a "JSON array" for short, but not an "array of JSON".

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;

Filtering out objects from multiple arrays in a JSONB column

If your postgres version is 12 or more, you can use the jsonpath language and functions. The query below returns the expected result with the subset of items and groups which match the given criteria. Then you can adapt this query within a sql function so that the search criteria is an input parameter.

SELECT jsonb_set(jsonb_set( data
                          , '{items}'
                          , jsonb_path_query_array(data, '$.items[*] ? (@.itemId == "123" && @.price == "10.00")'))
                , '{groups}'
                , jsonb_path_query_array(data, '$.groups[*] ? (@.groupId == "B" && @.discount == "20" && @.discountId == "3")'))
  FROM (SELECT
'{
  "prop1": "abc",
  "prop2": "xyz",
  "items": [
    {
      "itemId": "123",
      "price": "10.00"
    },
    {
      "itemId": "124",
      "price": "9.00"
    },
    {
      "itemId": "125",
      "price": "8.00"
    }
  ],
  "groups": [
    {
      "groupId": "A",
      "discount": "20",
      "discountId": "1"
    },
    {
      "groupId": "B",
      "discount": "30",
      "discountId": "2"
    },
    {
      "groupId": "B",
      "discount": "20",
      "discountId": "3"
    },
    {
      "groupId": "C",
      "discount": "40",
      "discountId": "4"
    }
  ]
}' :: jsonb) AS d(data)
WHERE jsonb_path_exists(data, '$.items[*] ? (@.itemId == "123" && @.price == "10.00")')
  AND jsonb_path_exists(data, '$.groups[*] ? (@.groupId == "B" && @.discount == "20" && @.discountId == "3")')

how to extract all keys in json array into column when select in PostgreSQL

In the first step, you should know that naming in this database is in lower case.

jsonb stores data in a decomposed binary form; that is, not as an ASCII/UTF-8 string, but as binary code.

in conclusion :

SELECT
    e.ID,
    e.first_name,
    e.last_name,
    j.MEMBER -> 'role' AS ROLE,
    j.MEMBER -> 'name' AS NAME 
FROM
    employeefamily e
    CROSS JOIN jsonb_array_elements (e.FAMILY) j(MEMBER)

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

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

convert to jsonb and use ? operator (Postgres document)

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

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

With any

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

With @>

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

Query JSONb Column Containing Array of JSON Objects