How to Return a JSONb Array and Array of Objects from My Data

How to return an array of JSON objects rather the a collection of rows using JOOQ & PostgreSQL

SQL Server FOR JSON semantics

That's precisely what the SQL Server FOR JSON clause does, which jOOQ supports and which it can emulate for you on other dialects as well:

ctx.select(T.A, T.B)
   .from(T)
   .forJSON().path()
   .fetch();

PostgreSQL native functionality

If you prefer using native functions directly, you will have to do with plain SQL templating for now, as some of these functions aren't yet supported by jOOQ, including:

• JSONB_PRETTY (no plans of supporting it yet)
• ARRAY_TO_JSON (https://github.com/jOOQ/jOOQ/issues/12841)
• ROW_TO_JSON (https://github.com/jOOQ/jOOQ/issues/10685)

It seems quite simple to write a utility that does precisely this:

public static ResultQuery<Record1<JSONB>> json(Select<?> subquery) {
    return ctx
        .select(field(
            "jsonb_pretty(array_to_json(array_agg(row_to_json(r)))::jsonb)", 
            JSONB.class
         ))
        .from(subquery.asTable("r"))
}

And now, you can execute any query in your desired form:

JSONB result = ctx.fetchValue(json(select(T.A, T.B).from(T)));

Converting between PG arrays and JSON arrays

A note on performance. It seems that you're converting between data types a bit often. Specifically, I'd suggest you avoid aggregating a PostgreSQL array and turning that into a JSON array, but to use JSONB_AGG() directly. I haven't tested this, but it seems to me that the extra data structure seems unnecessary.

How do I return a jsonb array and array of objects from my data?

You treat the result of the first join as JSON, not as text string, so use jsonb_each() instead of jsonb_each_text():

SELECT t.employee, json_object_agg(a.k, d.value) AS sales
FROM   mytable t
JOIN   LATERAL jsonb_each(t.data) a(k,v) ON a.k LIKE 'sales_%'
JOIN   LATERAL jsonb_to_recordset(a.v) d(yr text, value float) ON d.yr = '2012'
WHERE  t.employee = 'Jim'  -- works because employee is unique
GROUP  BY 1;

GROUP BY 1 is shorthand for GROUP BY t.employee.

Result:

employee | sales
---------+--------
Jim      | '{ "sales_tv" : 40, "sales_radio" : 76 }'

I also untangled and simplified your query.

json_object_agg() is instrumental in aggregating name/value pairs as JSON object. Optionally cast to jsonb if you need that - or use jsonb_object_agg() in Postgres 9.5 or later.

Using explicit JOIN syntax to attach conditions in their most obvious place.

The same without explicit JOIN syntax:

SELECT t.employee, json_object_agg(a.k, d.value) AS sales
FROM   mytable t
     , jsonb_each(t.data)      a(k,v) 
     , jsonb_to_recordset(a.v) d(yr text, value float)
WHERE  t.employee = 'Jim'
AND    a.k LIKE 'sales_%'
AND    d.yr = '2012'
GROUP  BY 1;

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 do I return an array of objects from jsonb?

The principle is the same as the question you asked yesterday, the first query (even though this question is yesterday's second query): peel away layers of hierarchy in your json data and then re-assemble it with whatever data you are interested in, into whatever new json format.

SELECT employee, json_object_agg(k, jarr) AS sales
FROM (
  SELECT t.employee, a.k,
         json_agg(json_build_object('value', d.value, 'yr', d.yr)) AS jarr
  FROM mytable t,
       jsonb_each(t.data) a(k, v),
       jsonb_to_recordset(a.v) d(yr text, value float)
  WHERE t.employee = 'Jim'
    AND a.k like 'sales_%'
  GROUP BY 1, 2) sub
GROUP BY 1; 

In the FROM clause you break down the JSON hierarchy with functions like jsonb_each and jsonb_to_recordset. As the last function's name already implies, each of these produces a set of records that you can work with just like you would with any other table and its columns. In the column selection list you select the required data and the appropriate aggregate functions json_agg and json_object_agg to piece the JSON result back together. For every level of hierarchy you need one aggregate function and therefore one level of sub-query.

Postgres Where Value in Json Array of Objects

The ANY takes a PostgreSQL array, not an JSON array. Because JSON arrays can have heterogeneous types, they are not trivially convertible to PostgreSQL arrays. You could create a help function to do this conversion.

create function jsonb_to_intarray(jsonb,property text) returns int[] immutable parallel safe language SQL as $$
  select array_agg((x->>$2)::int) from jsonb_array_elements($1) f(x)
$$;

And then:

SELECT * FROM table WHERE typeid =ANY (jsonb_to_intarray(jsondata -> 'Types', 'TypeID'));

SQL query to filter where all array items in JSONB array meet condition

The following worked well for me and allowed me the flexibility to use different comparison operators other than just ones such as == or <=.

In one of the scenarios I needed to construct, I also needed to have my WHERE in the subquery also compare against an array of values using the IN comparison operator, which was not viable using some of the other solutions that were looked at.

Leaving this here in case others run into the same issue as I did, or if others find better solutions or want to propose suggestions to build upon this one.

SELECT *
FROM sessions
WHERE NOT EXISTS (
    SELECT sessions.*
    FROM sessions
    INNER JOIN LATERAL (
        SELECT *
        FROM jsonb_to_recordset(sessions.snapshot->'cart')
        AS product(
            "product_id" integer,
            "name" varchar,
            "price_in_cents" integer
        )
    ) AS cart ON true
    WHERE name ILIKE "Product%";
)

Access a JSONB array of objects as an object of arrays

You can use the jsonb_array_elements to extract each object, aggregate those you want in an array using ARRAY_AGG and then convert that into a json array using array_to_json:

WITH j(json) AS (
  VALUES ('[
    {
      "A": "some value",
      "B": "another value",
      "foo": "bar",
      "x": "y"
    },
    {
      "B": "abc",
      "C": "asdf"
    }
  ]'::jsonb)
)
SELECT array_to_json(ARRAY_AGG(elem->'B'))
FROM j, jsonb_array_elements(json) elem
;
      array_to_json
-------------------------
 ["another value","abc"]
(1 row)

---

WITH j(json) AS (
  VALUES ('[
    {
      "A": "some value",
      "B": "another value",
      "foo": "bar",
      "x": "y"
    },
    {
      "B": "abc",
      "C": "asdf"
    }
  ]'::jsonb)
)
SELECT array_to_json(ARRAY_AGG(elem->'foo'))
FROM j, jsonb_array_elements(json) elem
;
 array_to_json
---------------
 ["bar",null]
(1 row)

Querying a JSON array of objects in Postgres

you have to unnest the array of json-objects first using the function (json_array_elements or jsonb_array_elements if you have jsonb data type), then you can access the values by specifying the key.

WITH json_test (col) AS (
  values (json '[{"name":"Mickey Mouse","age":10},{"name":"Donald Duck","age":5}]')
)
SELECT
  y.x->'name' "name"
FROM json_test jt, 
LATERAL (SELECT json_array_elements(jt.col) x) y

-- outputs:
name
--------------
"Mickey Mouse"
"Donald Duck"

To get a count of unique names, its a similar query to the above, except the count distinct aggregate function is applied to y.x->>name

WITH json_test (col) AS (
  values (json '[{"name":"Mickey Mouse","age":10},{"name":"Donald Duck","age":5}]')
)
SELECT
  COUNT( DISTINCT y.x->>'name') distinct_names
FROM json_test jt, 
LATERAL (SELECT json_array_elements(jt.col) x) y

It is necessary to use ->> instead of -> as the former (->>) casts the extracted value as text, which supports equality comparison (needed for distinct count), whereas the latter (->) extracts the value as json, which does not support equality comparison.

Alternatively, convert the json as jsonb and use jsonb_array_elements. JSONB supports the equality comparison, thus it is possible to use COUNT DISTINCT along with extraction via ->, i.e.

COUNT(DISTINCT (y.x::jsonb)->'name')

---

updated answer for postgresql versions 12+

It is now possible to extract / unnest specific keys from a list of objects using jsonb path queries, so long as the field queried is jsonb and not json.

example:

WITH json_test (col) AS (
  values (jsonb '[{"name":"Mickey Mouse","age":10},{"name":"Donald Duck","age":5}]')
)
SELECT jsonb_path_query(col, '$[*].name') "name"
FROM json_test
-- replaces this original snippet:
-- SELECT
--   y.x->'name' "name"
-- FROM json_test jt, 
-- LATERAL (SELECT json_array_elements(jt.col) x) y

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)

---

Related Topics