PostGreSQL 9.5.1 語法請教

psotgresql unpivot

noway 2025-02-15 17:02:19 ‧ 1165 瀏覽

分享至

您好：
因為在colpit查過，都卡死，所以在此請教一下前輩們是否可以解惑

 "Id","LotsId","Data" 1 ,100,"{"Result": [[0, 0, 3, 10, 11,], [0, 0, 9, 0, 0,], [1, 0, 0, 0, 0, ][, "Settings": [500, 4000, 100]}"

要如何轉成

"Id","LotsId","key", "grade","val" 
1 ,100,A, 1 ,0 
1 ,100,A, 2 ,0 
1 ,100,A, 3 ,3 
1 ,100,A, 4 ,10 
1 ,100,A, 5 ,1 
1 ,100,B, 1 ,0 
1 ,100,B, 2 ,0 
1 ,100,B, 3 ,9 
1 ,100,B, 4 ,0 
1 ,100,B, 5 ,0 
1 ,100,B, 1 ,1 
1 ,100,B, 2 ,0 
1 ,100,B, 3 ,0 
1 ,100,B, 4 ,0 
1 ,100,B, 5 ,0

第一段 Data抓出來的是 Character Varying(10000) 型態

SELECT 
        "Id",
        "LotsId",        
	     --"Data"
         jsonb_array_elements(("Data"::jsonb->'Result')::jsonb) AS result_array
    FROM  "AA"

jsonb_array_elements(("Data"::jsonb->'Result')::jsonb) AS result_array
他抓出來，並展開
"Id","LotsId", result_array
1 ,100, [0, 0, 3, 10, 11,],
1 ,100, [0, 0, 9, 0, 0,],
1 ,100, [1, 0, 0, 0, 0, ]
但，他沒有根據陣列排序，加一個欄位
"Id","LotsId", result_array ,index
1 ,100, [0, 0, 3, 10, 11,], 1
1 ,100, [0, 0, 9, 0, 0,], 2
1 ,100, [1, 0, 0, 0, 0, ],3
有row_number() OVER (PARTITION BY "Id", "LotsId" ORDER BY (SELECT NULL)) AS XX

但就一直卡死在這邊
ERROR: failed to find conversion function from unknown to text
SQL state: XX000

再請指導一下
謝謝

一級屠豬士 iT邦大師 1 級 ‧ 2025-02-15 23:12:42 檢舉

登入發表討論

熱門推薦

{{ item.channelVendor }} | {{ item.webinarstarted }} |

直播中

3 個回答

一級屠豬士

iT邦大師 1 級 ‧ 2025-02-16 00:06:03

按我前往

create table it0215 (
  id int
, lotid int
, data json
);

insert into it0215 values
(1, 100, '{"Result": [[0, 0, 3, 10, 11], [0, 0, 9, 0, 0], [1, 0, 0, 0, 0]], "Settings": [500, 4000, 100]}' );

with t1 as (
select id, lotid
     , json_array_elements(data -> 'Result') as result_array
  from it0215
)
select id, lotid
     , grade::int
     , value::int
  from t1
  cross join json_array_elements_text(result_array) with ordinality t(value, grade);

 id | lotid | grade | value
----+-------+-------+-------
  1 |   100 |     1 | 0
  1 |   100 |     2 | 0
  1 |   100 |     3 | 3
  1 |   100 |     4 | 10
  1 |   100 |     5 | 11
  1 |   100 |     1 | 0
  1 |   100 |     2 | 0
  1 |   100 |     3 | 9
  1 |   100 |     4 | 0
  1 |   100 |     5 | 0
  1 |   100 |     1 | 1
  1 |   100 |     2 | 0
  1 |   100 |     3 | 0
  1 |   100 |     4 | 0
  1 |   100 |     5 | 0
(15 rows)

你的key A,B 那個,看不出來.

回應 2
分享
檢舉

noway iT邦研究生 1 級 ‧ 2025-02-17 11:34:32 檢舉

您好：
, json_array_elements(data -> 'Result') as result_array
這一段就有問題了
我這邊欄位需要「"」圈起
json_array_elements("Data"->'Result') as result_array

Data 那一個欄位我看是 character varing
謝謝

ERROR:  operator does not exist: character varying -> unknown
LINE 8:        json_array_elements("Data"->'Result') as result_array
                                         ^
HINT:  No operator matches the given name and argument type(s). You might need to add explicit type casts.
SQL state: 42883
Character: 224

noway iT邦研究生 1 級 ‧ 2025-02-17 13:03:23 檢舉

您好：
最後有依據您的指導，修改如下

WITH ResultCTE AS (
  SELECT
    Id,
    Lotid,
    json_array_elements(Data::json -> 'Result') AS result_array,
    generate_series(1, json_array_length(Data::json -> 'Result')) AS index1
  FROM it0215
),
FlattenedCTE AS (
  SELECT
    Id,
    Lotid,
    index1,
    generate_series(1, json_array_length(result_array::json)) AS index2,
    json_array_elements(result_array::json) AS value
  FROM ResultCTE
)
SELECT
  Id,
  Lotid,
  index1,
  index2,
  value::text::int as value
FROM  FlattenedCTE

應該就可以了
謝謝

登入發表回應