时间戳

首页 > 解决方案 > PostgreSQL：计算特定的 JSON 元素

arrays - PostgreSQL：计算特定的 JSON 元素

问题描述

我想搜索和计算 json 数组中的特定元素。数组并不总是具有相同的长度。

当前服务器版本：9.6.12

我的表如下所示：

| id | value
+----+--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
|  1 | {"timeline":{"events":[{"date":1552289433501,"types":["START"]},{"date":1552291643428,"types":["STOP"]}]},"ongoingSLAData":null,"completeSLAData":[],"metricId":26,"definitionChangeDate":1553074745838,"definitionChangeMsEpoch":1553074745838,"goalsChangeDate":null,"goalsChangeMsEpoch":null,"goalTimeUpdatedDate":null,"goalTimeUpdatedMsEpoch":null,"metricCreatedDate":null,"updatedDate":1553074858390}
|  2 | {"timeline":{"events":[{"date":1552289433501,"types":["START"]},{"date":1552291643428,"types":["STOP"]}]},"ongoingSLAData":null,"completeSLAData":[{"succeeded":true,"goalTime":3600000,"goalTimeUnits":{"weeks":0,"remainingDaysWithinWeek":0,"remainingMillisWithinDay":3600000,"breached":false},"elapsedTime":443428,"remainingTime":3156572,"remainingTimeInDaysAndMillis":{"weeks":0,"remainingDaysWithinWeek":0,"remainingMillisWithinDay":3156572,"breached":false},"startTime":1552289433630,"stopTime":1552291643428},{"succeeded":false,"goalTime":3600000,"goalTimeUnits":{"weeks":0,"remainingDaysWithinWeek":0,"remainingMillisWithinDay":3600000,"breached":false},"elapsedTime":4069721,"remainingTime":-469721,"remainingTimeInDaysAndMillis":{"weeks":0,"remainingDaysWithinWeek":0,"remainingMillisWithinDay":469721,"breached":true},"startTime":1552986606597,"stopTime":1552990676318}],"metricId":26,"definitionChangeDate":1553074745838,"definitionChangeMsEpoch":1553074745838,"goalsChangeDate":null,"goalsChangeMsEpoch":null,"goalTimeUpdatedDate":null,"goalTimeUpdatedMsEpoch":null,"metricCreatedDate":null,"updatedDate":1553074858390}|
|  3 | {"timeline":{"events":[{"date":1546589579130,"types":["START"]},{"date":1546595500799,"types":["STOP"]}]},"ongoingSLAData":null,"completeSLAData":[{"succeeded":false,"goalTime":3600000,"goalTimeUnits":{"weeks":0,"remainingDaysWithinWeek":0,"remainingMillisWithinDay":3600000,"breached":false},"elapsedTime":5921454,"remainingTime":-2321454,"remainingTimeInDaysAndMillis":{"weeks":0,"remainingDaysWithinWeek":0,"remainingMillisWithinDay":2321454,"breached":true},"startTime":1546589579345,"stopTime":1546595500799}],"metricId":26,"definitionChangeDate":1553074745838,"definitionChangeMsEpoch":1553074745838,"goalsChangeDate":null,"goalsChangeMsEpoch":null,"goalTimeUpdatedDate":null,"goalTimeUpdatedMsEpoch":null,"metricCreatedDate":null,"updatedDate":1553074843707}|

SQL：

DROP TABLE IF EXISTS "sla_data";
DROP SEQUENCE IF EXISTS sla_data_id_seq;

CREATE SEQUENCE sla_data_id_seq 
INCREMENT 1 MINVALUE 1 MAXVALUE 9223372036854775807 START 3 CACHE 1;

CREATE TABLE "public"."sla_data" 
(
    "id" integer DEFAULT nextval('sla_data_id_seq') NOT NULL,
    "sladata" text
) WITH (oids = false);

TRUNCATE "sla_data";

INSERT INTO "sla_data" ("id", "sladata") 
VALUES (2, '{"timeline":{"events":[{"date":1552289433501,"types":["START"]},{"date":1552291643428,"types":["STOP"]}]},"ongoingSLAData":null,"completeSLAData":[{"succeeded":true,"goalTime":3600000,"goalTimeUnits":{"weeks":0,"remainingDaysWithinWeek":0,"remainingMillisWithinDay":3600000,"breached":false},"elapsedTime":443428,"remainingTime":3156572,"remainingTimeInDaysAndMillis":{"weeks":0,"remainingDaysWithinWeek":0,"remainingMillisWithinDay":3156572,"breached":false},"startTime":1552289433630,"stopTime":1552291643428},{"succeeded":false,"goalTime":3600000,"goalTimeUnits":{"weeks":0,"remainingDaysWithinWeek":0,"remainingMillisWithinDay":3600000,"breached":false},"elapsedTime":4069721,"remainingTime":-469721,"remainingTimeInDaysAndMillis":{"weeks":0,"remainingDaysWithinWeek":0,"remainingMillisWithinDay":469721,"breached":true},"startTime":1552986606597,"stopTime":1552990676318}],"metricId":26,"definitionChangeDate":1553074745838,"definitionChangeMsEpoch":1553074745838,"goalsChangeDate":null,"goalsChangeMsEpoch":null,"goalTimeUpdatedDate":null,"goalTimeUpdatedMsEpoch":null,"metricCreatedDate":null,"updatedDate":1553074858390}'),
(3, '{"timeline":{"events":[{"date":1546589579130,"types":["START"]},{"date":1546595500799,"types":["STOP"]}]},"ongoingSLAData":null,"completeSLAData":[{"succeeded":false,"goalTime":3600000,"goalTimeUnits":{"weeks":0,"remainingDaysWithinWeek":0,"remainingMillisWithinDay":3600000,"breached":false},"elapsedTime":5921454,"remainingTime":-2321454,"remainingTimeInDaysAndMillis":{"weeks":0,"remainingDaysWithinWeek":0,"remainingMillisWithinDay":2321454,"breached":true},"startTime":1546589579345,"stopTime":1546595500799}],"metricId":26,"definitionChangeDate":1553074745838,"definitionChangeMsEpoch":1553074745838,"goalsChangeDate":null,"goalsChangeMsEpoch":null,"goalTimeUpdatedDate":null,"goalTimeUpdatedMsEpoch":null,"metricCreatedDate":null,"updatedDate":1553074843707}'),
(1, '{"timeline":{"events":[{"date":1552289433501,"types":["START"]},{"date":1552291643428,"types":["STOP"]}]},"ongoingSLAData":null,"completeSLAData":[{"succeeded":false,"goalTime":3600000,"goalTimeUnits":{"weeks":0,"remainingDaysWithinWeek":0,"remainingMillisWithinDay":3600000,"breached":false},"elapsedTime":5921454,"remainingTime":-2321454,"remainingTimeInDaysAndMillis":{"weeks":0,"remainingDaysWithinWeek":0,"remainingMillisWithinDay":2321454,"breached":true},"startTime":1546589579345,"stopTime":1546595500799}],"metricId":26,"definitionChangeDate":1553074745838,"definitionChangeMsEpoch":1553074745838,"goalsChangeDate":null,"goalsChangeMsEpoch":null,"goalTimeUpdatedDate":null,"goalTimeUpdatedMsEpoch":null,"metricCreatedDate":null,"updatedDate":1553074858390}');

我的 SQL 查询目前看起来像这样，但我只捕获了第一个元素：

SELECT 
    CAST(sladata::jsonb -> 'completeSLAData' -> 0 ->> 'succeeded' AS TEXT) AS metric,
    COUNT(sladata::jsonb -> 'completeSLAData' -> 0 -> 'succeeded') AS value
FROM 
    sla_data
GROUP BY 
    sladata::jsonb -> 'completeSLAData' -> 0 ->> 'succeeded'

标签： arraysjsonpostgresql

解决方案

正如@Bergi 提到的，这样做的方法是使用横向连接。

SELECT succeeded as metric, count(succeeded) as value 
FROM (
  SELECT id, succeeded
  FROM sla_data
  CROSS JOIN LATERAL (
    SELECT j->> 'succeeded' as succeeded FROM jsonb_array_elements(sladata::jsonb -> 'completeSLAData') a(j)
  ) AS sub
) AS sub2
GROUP BY succeeded;
 metric | value
--------+-------
 false  |     3
 true   |     1

如果你自己运行内部查询，你可以看到它在做什么：

SELECT id, succeeded
  FROM sla_data
  CROSS JOIN LATERAL (
    SELECT j->> 'succeeded' as succeeded FROM jsonb_array_elements(sladata::jsonb -> 'completeSLAData') a(j)
  ) AS sub;
 id | succeeded
----+-----------
  2 | true
  2 | false
  3 | false
  1 | false
(4 rows)

推荐阅读