How to count the number of nested array elements in each JSON array in SQL result?-CodePudding

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?

The current SQL query I'm running is this

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

I have this now based on Laurenz' comment but i get a "GROUP" syntax error...

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

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 json_array_elements(q.metadata) AS a(e)
  GROUP BY q.object_uuid,
           q.username,
           q.image_uuid,
           q.metadata,
           q.user_uuid
 ) AS p;

CodePudding user response：

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;