sql - 需要根据列值分配不同的JSON数据结构

Question

CREATE TABLE public.temp_test (
    "part_number" character varying(255),
    "service" character varying(255),
    "label" text,
    "value" double precision
);

INSERT INTO public.temp_test ("part_number", "service", "label", "value") VALUES ('ABCDSC', 'type1', '2019-05-13', 1100) ,('ABCDSC', 'type1', '2019-05-14', 1500) ,('ABCDSC', 'type1', '2019-05-15', 1300) ,('ABCDE', 'type1', '2019-05-16', 1300) ,('ABCDSC', 'type1', '2019-05-17', 1400) ,('ABCDSC', 'type1', '2019-05-18', 1400) ,('ABCDSC', 'type1', '2019-05-19', 800) ,('ABCDSC', 'type1', '2019-05-20', 1257) ,('ABCDSC', 'type1', '2019-05-21', 1100) ,('ABCDSC', 'type1', '2019-05-22', 1200) ,('ABCDSC', 'type1', '2019-05-23', 2860) ,('ABCDSC', 'type1', '2019-05-27', 1634) ,('ABCDSC', 'type1', '2019-06-06', 396) ,('QWERTY', 'type2', '2019-05-13', 200) ,('QWERTY', 'type2', '2019-05-14', 100) ,('QWERTY', 'type2', '2019-05-15', 300) ,('QWERTY', 'type2', '2019-05-16', 100) ,('QWERTY', 'type2', '2019-05-17', 100) ,('QWERTY', 'type2', '2019-05-18', 100) ,('QWERTY', 'type2', '2019-05-19', 100) ,('QWERTY', 'type2', '2019-05-20', 107) ,('QWERTY', 'type2', '2019-05-21', 200) ,('QWERTY', 'type2', '2019-05-22', 100) ,('QWERTY', 'type2', '2019-05-23', 280) ,('QWERTY', 'type2', '2019-05-27', 160) ,('QWERTY', 'type2', '2019-06-06', 3659);

这是我进入 temp_test 的数据

--  part_number | service |    label   |    value 
-- -------------+ ------- +------------+-------------
--  ABCDSC      |  type1  | 2019-05-13 |        1100
--  ABCDSC      |  type1  | 2019-05-14 |        1500
--  ABCDSC      |  type1  | 2019-05-15 |        1300
--  ABCDSC      |  type1  | 2019-05-16 |        1300
--  ABCDSC      |  type1  | 2019-05-17 |        1400
--  ABCDSC      |  type1  | 2019-05-18 |        1400
--  ABCDSC      |  type1  | 2019-05-19 |         800
--  ABCDSC      |  type1  | 2019-05-20 |        1257
--  ABCDSC      |  type1  | 2019-05-21 |        1100
--  ABCDSC      |  type1  | 2019-05-22 |        1200
--  ABCDSC      |  type1  | 2019-05-23 |        2860
--  ABCDSC      |  type1  | 2019-05-27 |        1634
--  ABCDSC      |  type1  | 2019-06-06 |         396
--  QWERTY      |  type2  | 2019-05-13 |         200
--  QWERTY      |  type2  | 2019-05-14 |         100
--  QWERTY      |  type2  | 2019-05-15 |         300
--  QWERTY      |  type2  | 2019-05-16 |         100
--  QWERTY      |  type2  | 2019-05-17 |         100
--  QWERTY      |  type2  | 2019-05-18 |         100
--  QWERTY      |  type2  | 2019-05-19 |         100
--  QWERTY      |  type2  | 2019-05-20 |         107
--  QWERTY      |  type2  | 2019-05-21 |         200
--  QWERTY      |  type2  | 2019-05-22 |         100
--  QWERTY      |  type2  | 2019-05-23 |         280
--  QWERTY      |  type2  | 2019-05-27 |         160
--  QWERTY      |  type2  | 2019-06-06 |        3659

但在 PSQL 函数中，我需要将其分配给两个键 DailyPart1Usage 和 DailyPart2Usage

---

CREATE TYPE DATA AS(
    "DailyPart1Usage"            JSONB,
    "DailyPart2Usage"            JSONB
);

DROP FUNCTION IF EXISTS "GetData"(INTEGER,BIGINT,TEXT,JSON,TEXT,TEXT,TEXT);
CREATE OR REPLACE FUNCTION "GetData"()
RETURNS DATA AS $$
DECLARE

    daily_part_data  JSON;
    -- May be need 1 more data type here to distinguish between DailyPart1Usage, DailyPart2Usage.

BEGIN

    SELECT 
        array_to_json(array_agg(row_to_json(sq1))) INTO daily_part_data
    FROM (
        SELECT
            "value",
            "label"
        FROM
        temp_test
    ) AS sq1;

    RETURN account_dash_data;
END;
$$ LANGUAGE plpgsql;

---

现在只有daily_part_data包含所有零件类型数据

[{"label":"2019-05-13","value":1100},{"label":"2019-05-14","value":1500},{"label":"2019-05-15","value":1300},{"label":"2019-05-16","value":1300},{"label":"2019-05-17","value":1400},{"label":"2019-05-18","value":1400},{"label":"2019-05-19","value":800},{"label":"2019-05-20","value":1257},{"label":"2019-05-21","value":1100},{"label":"2019-05-22","value":1200},{"label":"2019-05-23","value":2860},{"label":"2019-05-27","value":1634},{"label":"2019-06-06","value":396}]

我需要下面的数据结构:

我尝试使用 CASE 构建 JSON 数据，但无法插入 DATA。

-- Expected Result in `DailyPart1Usage`, `DailyPart2Usage`
"DailyPart1Usage": {    --- Relatod to Part1: ABCDSC
    "ChartData": [
        {
            "value": 10,
            "label": "2019-05-07"
        },
        {
            "value": 20,
            "label": "2019-05-06"
        }
        ....
    ]
},
"DailyPart2Usage": {    --- Relatod to Part2: QWERTY
    "ChartData": [
        {
            "value": 100,
            "label": "2019-05-07"
        },
        {
            "value": 129,
            "label": "2019-05-06"
        }
        ....
    ]
}

注意:只有 2 个零件类型。

Answer 1

看看这个查询的结果，看看它是否能让你接近。

它为每个 part_number 生成 JSON 行，如下所示:

{"ChartData": [{"label": "2019-05-16", "value": 1300}, ...]}

您可以扩展它以包含像 DailyPart2Usage 这样的键，但似乎不清楚您打算如何将其与 part_number 相关联。

SELECT
  json_build_object(
    'ChartData',
    array_to_json(
      array_agg(
        json_build_object(
          'label', label,
          'value', value
        )
      )
    )
  )
FROM
  temp_test
GROUP BY
  part_number