arrays - 在 postgres 中查询 JSON[] 类型

Question

我在 postgres 中有一个表，其中包含一个数据类型为 json[] 的字段。该列存储的数据结构如下:

[{
    "sym": "BTC",
    "enn": "Bitcoin",
    "fan": "",
    "prc": 7284.46,
    "c24": -4.33,
    "mkc": 124460367747.02,
    "mkp": 0
}, {
    "sym": "ETH",
    "enn": "Ethereum",
    "fan": "",
    "prc": 571.735,
    "c24": -5.23,
    "mkc": 57166582578.235,
    "mkp": 0
}, {
    "sym": "XRP",
    "enn": "Ripple",
    "fan": "",
    "prc": 0.625291,
    "c24": -6.28,
    "mkc": 24539115471.842476,
    "mkp": 0
}, {
    "sym": "BCH",
    "enn": "Bitcoin Cash",
    "fan": "",
    "prc": 1034.65,
    "c24": -7.09,
    "mkc": 17771148400,
    "mkp": 0
}, {
    "sym": "EOS",
    "enn": "EOS",
    "fan": "",
    "prc": 13.2186,
    "c24": -7.95,
    "mkc": 11845841674.9512,
    "mkp": 0
}]

我需要的是使用特定的 "sym" 键获取 json。像这样:

{
    "sym": "BTC",
    "enn": "Bitcoin",
    "fan": "",
    "prc": 7284.46,
    "c24": -4.33,
    "mkc": 124460367747.02,
    "mkp": 0
}

我试过这个:

select to_json(data)::json ->'sym'->'BTC'from my_table;

但它不起作用。我知道它不起作用，因为我的字段是一个数组，而不是 json 并尝试了这个

select json_array_elements(to_json(data)::json->'sym'->'BTC') from my_table;

但它也不起作用。有帮助吗？

Answer 1

你用 CTE 试过了吗？ ？当然还有许多其他方法可以做到这一点，但我发现 CTE 非常优雅且易于阅读。

WITH j AS (
SELECT json_array_elements('[{"sym":"BTC","enn":"Bitcoin","fan":"","prc":7284.46,"c24":-4.33,"mkc":124460367747.02,"mkp":0},{"sym":"ETH","enn":"Ethereum","fan":"","prc":571.735,"c24":-5.23,"mkc":57166582578.235,"mkp":0},{"sym":"XRP","enn":"Ripple","fan":"","prc":0.625291,"c24":-6.28,"mkc":24539115471.842476,"mkp":0},{"sym":"BCH","enn":"Bitcoin Cash","fan":"","prc":1034.65,"c24":-7.09,"mkc":17771148400,"mkp":0},{"sym":"EOS","enn":"EOS","fan":"","prc":13.2186,"c24":-7.95,"mkc":11845841674.9512,"mkp":0}]'::json) AS sym
)
SELECT *
FROM j
WHERE j.sym->>'sym'= 'BTC';

                                              sym                                               
------------------------------------------------------------------------------------------------
 {"sym":"BTC","enn":"Bitcoin","fan":"","prc":7284.46,"c24":-4.33,"mkc":124460367747.02,"mkp":0}
(1 Zeile)