database - 在 PostgreSQL 中取消嵌套 JSON 对象列表

Question

我的 PostgreSQL (9.6) 数据库中有一个 TEXT 列，其中包含一个或多个词典的列表，例如那些词典。

[{"line_total_excl_vat": "583.3300", "account": "", "subtitle": "", "product_id": 5532548, "price_per_unit": "583.3333", "line_total_incl_vat": "700.0000", "text": "PROD0008", "amount": "1.0000", "vat_rate": "20"}]

或

[{"line_total_excl_vat": "500.0000", "account": "", "subtitle": "", "product_id": "", "price_per_unit": "250.0000", "line_total_incl_vat": "600.0000", "text": "PROD003", "amount": "2.0000", "vat_rate": "20"}, {"line_total_excl_vat": "250.0000", "account": "", "subtitle": "", "product_id": 5532632, "price_per_unit": "250.0000", "line_total_incl_vat": "300.0000", "text": "PROD005", "amount": "1.0000", "vat_rate": "20"}]

我想从列中检索每个字典并在不同的列中解析它们。

对于这个例子:

id | customer | blurb
---+----------+------
 1 | Joe      | [{"line_total_excl_vat": "583.3300", "account": "", "subtitle": "", "product_id": 5532548, "price_per_unit": "583.3333", "line_total_incl_vat": "700.0000", "text": "PROD0008", "amount": "1.0000", "vat_rate": "20"}]
 2 | Sally    | [{"line_total_excl_vat": "500.0000", "account": "", "subtitle": "", "product_id": "", "price_per_unit": "250.0000", "line_total_incl_vat": "600.0000", "text": "PROD003", "amount": "2.0000", "vat_rate": "20"}, {"line_total_excl_vat": "250.0000", "account": "", "subtitle": "", "product_id": 5532632, "price_per_unit": "250.0000", "line_total_incl_vat": "300.0000", "text": "PROD005", "amount": "1.0000", "vat_rate": "20"}]

会变成:

id | customer | line_total_excl_vat  | account |  product_id | ...
---+----------+----------------------+---------+------------
 1 | Joe      | 583.3300             |     null|      5532548  
 2 | Sally    | 500.0000             |     null|         null
 3 | Sally    | 250.0000             |     null|      5532632

Answer 1

如果您事先知道要提取哪些字段，请将文本转换为 json/jsonb 并使用 json_to_recordset/jsonb_to_recordset。请注意，此方法需要明确指定字段名称/类型。 json 字典中未指定的字段将不会被提取。

见官方postgesql documentation on json-functions

自包含示例:

WITH tbl (id, customer, dat) as ( values
     (1, 'Joe',
      '[{ "line_total_excl_vat": "583.3300"
        , "account": ""
        , "subtitle": ""
        , "product_id": 5532548
        , "price_per_unit": "583.3333"
        , "line_total_incl_vat": "700.0000"
        , "text": "PROD0008"
        , "amount": "1.0000"
        , "vat_rate": "20"}]')
    ,(2, 'Sally', 
      '[{ "line_total_excl_vat": "500.0000"
        , "account": ""
        , "subtitle": ""
        , "product_id": ""
        , "price_per_unit": "250.0000"
        , "line_total_incl_vat": "600.0000"
        , "text": "PROD003"
        , "amount": "2.0000"
        , "vat_rate": "20"}
      , { "line_total_excl_vat": "250.0000"
        , "account": ""
        , "subtitle": ""
        , "product_id": 5532632
        , "price_per_unit": "250.0000"
        , "line_total_incl_vat": "300.0000"
        , "text": "PROD005"
        , "amount": "1.0000"
        , "vat_rate": "20"}]')
)
SELECT id, customer, x.*
FROM tbl
   , json_to_recordset(dat::json) x 
        (  line_total_excl_vat numeric
         , acount text
         , subtitle text
         , product_id text
         , price_per_unit numeric
         , line_total_incl_vat numeric
         , "text" text
         , amount numeric
         , vat_rate numeric
        )

产生以下输出:

id    customer    line_total_excl_vat    acount    subtitle    product_id    price_per_unit    line_total_incl_vat    text      amount    vat_rate
 1    Joe                      583.33                             5532548    583.3333                          700    PROD0008       1          20
 2    Sally                       500                                             250                          600    PROD003        2          20
 2    Sally                       250                             5532632         250                          300    PROD005        1          20

此格式通常称为宽 格式。

也可以提取long 格式的数据，它的额外好处是它保留了所有数据而无需明确提及字段名称。在这种情况下，查询可以写成(为简洁起见省略了测试数据)

SELECT id, customer, y.key, y.value, x.record_number
FROM tbl
   , lateral json_array_elements(dat::json) WITH ORDINALITY AS x (val, record_number)
   , lateral json_each_text(x.val) y

上述语句中的with ordinality为未嵌套数组中的每个元素添加一个序号，用于为每个客户消除来自不同数组的字段的歧义。

这产生了输出:

id  customer key                    value     record_number
1   Joe      line_total_excl_vat    583.3300  1
1   Joe      account                          1
1   Joe      subtitle                         1
1   Joe      product_id             5532548   1
1   Joe      price_per_unit         583.3333  1
1   Joe      line_total_incl_vat    700.0000  1
1   Joe      text                   PROD0008  1
1   Joe      amount                 1.0000    1
1   Joe      vat_rate               20        1
2   Sally    line_total_excl_vat    500.0000  1
2   Sally    account                          1
2   Sally    subtitle                         1
2   Sally    product_id                       1
2   Sally    price_per_unit         250.0000  1
2   Sally    line_total_incl_vat    600.0000  1
2   Sally    text                   PROD003   1
2   Sally    amount                 2.0000    1
2   Sally    vat_rate               20        1
2   Sally    line_total_excl_vat    250.0000  2
2   Sally    account                          2
2   Sally    subtitle                         2
2   Sally    product_id             5532632   2
2   Sally    price_per_unit         250.0000  2
2   Sally    line_total_incl_vat    300.0000  2
2   Sally    text                   PROD005   2
2   Sally    amount                 1.0000    2
2   Sally    vat_rate               20        2