Count nested JSON array elements over all result rows

Question

I have a SQL query that I am running in order to get results, where one of the column contains a JSON array.

I want to count the total of JSON elements in total from all returned rows.

I.e. if 2 rows were returned, where one row had 3 JSON array items in metadata column, and the second row had 4 JSON array items in metadata column, I'd like to see 7 as a returned count.

Is this possible?

This is my current SQL query:

WITH _result AS (
   SELECT lo.*
   FROM laser.laser_checks la
      JOIN laser.laser_brands lo ON la.id = lo.brand_id
   WHERE lo.type not in (1)
     AND la.source in (1,4,5)
     AND la.prod_id in (1, 17, 19, 22, 27, 29)
)
SELECT ovr.json -> 'id' AS object_uuid,
       ovr.json -> 'username' AS username,
       image.KEY AS image_uuid,
       image.value AS metadata,
       user_id as user_uuid
FROM _result ovr,
     jsonb_array_elements(ovr."json" -> 'images') elem,
     jsonb_each(elem) image

Laurenz Albe · Accepted Answer · 2022-01-26 14:27:34Z

1

Unpack the arrays and count the elements:

WITH q AS (/* your query */)
SELECT object_uuid,
       username,
       image_uuid,
       metadata,
       user_uuid,
       sum(elemcount) OVER () AS total_array_elements
FROM (SELECT q.object_uuid,
             q.username,
             q.image_uuid,
             q.metadata,
             q.user_uuid,
             count(a.e) AS elemcount
      FROM q
         LEFT JOIN LATERAL jsonb_array_elements(q.metadata) AS a(e)
            ON TRUE
      GROUP BY q.object_uuid,
               q.username,
               q.image_uuid,
               q.metadata,
               q.user_uuid
     ) AS p;

edited Jan 26, 2022 at 14:27

answered Jan 26, 2022 at 11:49

Laurenz Albe

257k22 gold badges313 silver badges389 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

caasswa Over a year ago

Fabulous - thanks Laurenz, I need to really work on my SQL! Need to learn what LATERAL does etc...

Erwin Brandstetter · Accepted Answer · 2023-02-15 23:39:47Z

An elephant managed to slip everybody's attention in this room: jsonb_array_length().
(Or json_array_length() for json.)
The manual:

Returns the number of elements in the top-level JSON array.

After you have already unnested the JSON array to your level of interest, you can apply the function to the (now) top level. Wrap it in a window function to get total counts for every result row.
Your query should work like this:

SELECT lo.json -> 'id'       AS object_uuid
     , lo.json -> 'username' AS username
     , image.key             AS image_uuid
     , image.value           AS metadata
     , lo.user_id            AS user_uuid
     , sum(jsonb_array_length(image.value)) OVER () AS total_array_elements  -- !!!      
FROM   laser.laser_checks la
JOIN   laser.laser_brands lo ON la.id = lo.brand_id
     , jsonb_array_elements(lo."json" -> 'images') elem
     , jsonb_each(elem) image
WHERE  lo.type NOT IN (1)
AND    la.source IN (1,4,5)
AND    la.prod_id IN (1, 17, 19, 22, 27, 29);

No need for a LATERAL subquery, aggregation, nor even for a CTE, really.

Sort by length of nested JSON array

Collectives™ on Stack Overflow

Count nested JSON array elements over all result rows

2 Answers 2

1 Comment

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

1 Comment

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related