我有一個 SQL 查詢，我正在運行以獲取結果，其中一列包含一個 JSON 陣列。
我想從所有回傳的行中計算 JSON 元素的總數。
即如果回傳 2 行，其中一行在列中有 3 個 JSON 陣列項，metadata第二行在列中有 4 個 JSON 陣列項metadata，我希望看到 7 作為回傳計數。
這可能嗎？
我正在運行的當前 SQL 查詢是這個
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 的評論，但我得到一個“GROUP”語法錯誤......
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;
uj5u.com熱心網友回復：
解包陣列并計算元素：
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 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;
轉載請註明出處，本文鏈接：https://www.uj5u.com/qita/421595.html
標籤：