需要根据列值分配不同的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

Answer 2

感谢 @mike.k，使用他的回答，我能够使用以下查询获取所需的数据：

    SELECT 
        jsonb_object_agg(sq."service", "Data") INTO daily_usage_data
    FROM (
        SELECT
            temp_test."service",
            json_build_object(
                'ChartData',
                    array_to_json(
                    array_agg(
                            json_build_object(
                                'label', "label",
                                'value', "value"
                            )
                    )
                )
            )
            "Data"
        FROM temp_test
        GROUP BY temp_test."service"
    ) AS sq1;
    -- Assign Query data to DATA Keys.
    DATA."DailyPart1Usage"                       = daily_usage_data->>'type1';
    DATA."DailyPart2Usage"                       = daily_usage_data->>'type2';