将字典样式的字符串转换为PostgreSQL中的表格

huangapple go评论72阅读模式
英文:

Turn dictionary-like string into table in Postgre

问题

我有一个类似字典的字符串,如下所示:

> {100: {1:500000, 2: 600000}, 200: {1:700000, 2: 800000}, 300: {1:
> 900000, 2: 1000000}}

请注意,路径级别始终固定,但键值对的数量不固定。

我想将其转换成以下形式的表格:

列 1    | 列 2    | 列 3
----------------------------
100    | 1      | 500000
100    | 2      | 600000
200    | 1      | 700000
200    | 2      | 800000
300    | 1      | 900000
300    | 2      | 1000000

我尝试使用正则表达式 (select regexp_split_to_table(string, '[{}:, ]+') from ...) 并获取数字行,但将这些行转换为表格会引发另一个问题需要解决。

英文:

I have a dictionary-like string like this:

> {100: {1:500000, 2: 600000}, 200: {1:700000, 2: 800000}, 300: {1:
> 900000, 2: 1000000}}

Note that the path level is always fixed, but the number of key-value pairs is not.

I want to convert it into a table like this:

column 1 | column 2 | column 3
--------------------------------
100      | 1        | 500000
100      | 2        | 600000
200      | 1        | 700000
200      | 2        | 800000
300      | 1        | 900000
300      | 2        | 1000000

I've tried using regular expression (select regexp_split_to_table(string, '[{}:, ]+') from ...) and get rows of number, but to convert these rows into a table creates another problem to solve.

答案1

得分: 0

我们可以使用REGEXP_REPLACE将类似字典的字符串转换为有效的JSON,然后在两个级别应用jsonb_each来获取预期的输出:

SELECT n1.key as column_1, n2.key as column_2, n2.value as column_3
FROM mytable t, jsonb_each(REGEXP_REPLACE(jsoncol, '([0-9]+):', '"":', 'g')::jsonb) n1,
                jsonb_each(n1.value) n2

结果:

column_1	column_2	column_3
100	        1	        500000
100	        2	        600000
200	        1	        700000
200	        2	        800000
300	        1	        900000
300	        2	        1000000

在此处查看演示

英文:

We can convert the dictionary-like string into a valid JSON using REGEXP_REPLACE then apply jsonb_each in two levels to get the expected output :

SELECT n1.key as column_1, n2.key as column_2, n2.value as column_3
FROM mytable t, jsonb_each(REGEXP_REPLACE(jsoncol, '([0-9]+):', '"":', 'g')::jsonb) n1,
                jsonb_each(n1.value) n2

Result :

column_1	column_2	column_3
100	        1	        500000
100	        2	        600000
200	        1	        700000
200	        2	        800000
300	        1	        900000
300	        2	        1000000

Demo here

huangapple
  • 本文由 发表于 2023年6月12日 16:25:21
  • 转载请务必保留本文链接:https://go.coder-hub.com/76454789.html
匿名

发表评论

匿名网友

:?: :razz: :sad: :evil: :!: :smile: :oops: :grin: :eek: :shock: :???: :cool: :lol: :mad: :twisted: :roll: :wink: :idea: :arrow: :neutral: :cry: :mrgreen:

确定