PostGreSQL 9.5.1 语法请教

您好：因为在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_array1 ,100, [0, 0, 3, 10, 11,],1 ,100, [0, 0, 9, 0, 0,],1 ,100, [1, 0, 0, 0, 0, ]但，他没有 根据阵列 排序， 加一个栏位"Id","LotsId", result_array ,index1 ,100, [0, 0, 3, 10, 11,], 11 ,100, [0, 0, 9, 0, 0,], 21 ,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 textSQL state: XX000

再请指导一下谢谢

1 个回答

2

一级屠猪士

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

您好：<br /> , json_array_elements(data -> 'Result') as result_array<br /> 这一段就 有问题了<br /> 我这边栏位需要「"」圈起<br /> json_array_elements("Data"->'Result') as result_array</p> <p>Data 那一个栏位我看是 character varing<br /> 谢谢</p> <p>```<br /> ERROR: operator does not exist: character varying -> unknown<br /> LINE 8: json_array_elements("Data"->'Result') as result_array<br /> ^<br /> HINT: No operator matches the given name and argument type(s). You might need to add explicit type casts.<br /> SQL state: 42883<br /> Character: 224<br /> ```

修改

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

应该就可以了
谢谢

您好：<br /> 最后有依据您的指导，修改如下<br /> ```<br /> WITH ResultCTE AS (<br /> SELECT<br /> Id,<br /> Lotid,<br /> json_array_elements(Data::json -> 'Result') AS result_array,<br /> generate_series(1, json_array_length(Data::json -> 'Result')) AS index1<br /> FROM it0215<br /> ),<br /> FlattenedCTE AS (<br /> SELECT<br /> Id,<br /> Lotid,<br /> index1,<br /> generate_series(1, json_array_length(result_array::json)) AS index2,<br /> json_array_elements(result_array::json) AS value<br /> FROM ResultCTE<br /> )<br /> SELECT<br /> Id,<br /> Lotid,<br /> index1,<br /> index2,<br /> value::text::int as value<br /> FROM FlattenedCTE<br /> ```<br /> 应该就可以了<br /> 谢谢

修改