postgresql 在SQL中按字段类型对JSON值排序

qyzbxkaa 于 2022-12-18 发布在 PostgreSQL

关注(0)|答案(1)|浏览(217)

我有两张table：

attribute
id          project_id  name          type
"556e28ab"  "14b106fd"  "running_id"  "INTEGER"
"7c78e6a4"  "14b106fd"  "headline"    "TEXT"

record
id          project_id  data
"63d7af84"  "14b106fd"  "{""headline"": ""Mike Tyson set to retire after loss"", ""running_id"": 0}"
"055e85ff"  "14b106fd"  "{""headline"": ""Iraqi vote remains in doubt"", ""running_id"": 1}"
"2dd0acdc"  "14b106fd"  "{""headline"": ""Conservatives Ponder Way Out of Wilderness"", ""running_id"": 2}"
"d8b82916"  "14b106fd"  "{""headline"": ""Final report blames instrument failure for Adam Air Flight 574 disaster"", ""running_id"": 3}"
"a71268cf"  "14b106fd"  "{""headline"": ""Kim Sets a Skating Record and Wins Her First World Title"", ""running_id"": 4}"

我想在数据json中按running_id对值进行排序，如果这样做，排序将把running_id视为STRING，而不是应该视为INTEGER。

SELECT *
FROM record r
WHERE r.project_id = '14b106fd'
ORDER BY (r.data ->>'running_id') ASC;

我可以将其强制转换为INTEGER，但这不是一个好的解决方案，因为我希望能够按json中的任何字段排序，而这些字段不是我定义的。我希望找到一种方法来强制转换它，而无需显式定义类型或使用attribute表中的类型。我已经试过了，但是它被解释为STRING而不是type。
我也已经尝试了下面的代码：

SELECT r.*, a.data_type
FROM record r
JOIN attribute a ON (a.name = 'running_id' AND a.project_id = r.project_id)
WHERE r.project_id = '14b106fd'
ORDER BY
    CASE CAST(a.data_type AS TEXT)
        WHEN 'INTEGER' THEN CAST(r.data ->> 'running_id' AS INTEGER)
        WHEN 'NUMERIC' THEN CAST(r.data ->> 'running_id' AS NUMERIC)
        ELSE r.data ->>'running_id'
    END;

型

postgresql

来源：https://stackoverflow.com/questions/74789224/sorting-json-values-by-field-type-in-sql

1条答案

按热度按时间

cuxqih211#

假设您有另一个json字段running_txt，它是json字符串数据类型。

按整数数据类型running_id排序：

select id,
       project_id,
       data
  from ibiscp_record
 order by case when json_typeof(data->'running_id')='number' then cast(data->>'running_id' as integer) end , data->>'running_id';

结果：

id      |project_id|data                                                                                                                        |
--------+----------+----------------------------------------------------------------------------------------------------------------------------+
63d7af84|14b106fd  |{"headline": "Mike Tyson set to retire after loss", "running_id": 0, "running_txt": "0"}                                    |
2dd0acdc|14b106fd  |{"headline": "Conservatives Ponder Way Out of Wilderness", "running_id": 2, "running_txt": "2"}                             |
d8b82916|14b106fd  |{"headline": "Final report blames instrument failure for Adam Air Flight 574 disaster", "running_id": 3, "running_txt": "3"}|
a71268cf|14b106fd  |{"headline": "Kim Sets a Skating Record and Wins Her First World Title", "running_id": 4, "running_txt": "4"}               |
055e85ff|14b106fd  |{"headline": "Iraqi vote remains in doubt", "running_id": 11, "running_txt": "11"}                                          |

按running_txt排序为字符串数据类型（需要将json字段名从running_id更改为running_txt，其余查询保持不变）

select id,
       project_id,
       data
  from ibiscp_record
 order by case when json_typeof(data->'running_txt')='number' then cast(data->>'running_txt' as integer) end , data->>'running_txt';

型
结果：

id      |project_id|data                                                                                                                        |
--------+----------+----------------------------------------------------------------------------------------------------------------------------+
63d7af84|14b106fd  |{"headline": "Mike Tyson set to retire after loss", "running_id": 0, "running_txt": "0"}                                    |
055e85ff|14b106fd  |{"headline": "Iraqi vote remains in doubt", "running_id": 11, "running_txt": "11"}                                          |
2dd0acdc|14b106fd  |{"headline": "Conservatives Ponder Way Out of Wilderness", "running_id": 2, "running_txt": "2"}                             |
d8b82916|14b106fd  |{"headline": "Final report blames instrument failure for Adam Air Flight 574 disaster", "running_id": 3, "running_txt": "3"}|
a71268cf|14b106fd  |{"headline": "Kim Sets a Skating Record and Wins Her First World Title", "running_id": 4, "running_txt": "4"}               |

赞(0）回复(0）举报 2022-12-18

我来回答

postgresql 在SQL中按字段类型对JSON值排序

1条答案

相关问题

热门标签

最新问答