按 JSON 数据类型 postgres 排序
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/25967778/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
order by JSON data type postgres
提问by user1767105
I have a Postgres table which has column of type JSON which has a bunch of JSON objects in them. I want to query the table records and order the results by a value stored in the JSON field. I'm getting the queries to run, but they don't sort correctly. I'm not finding a ton of documentation on ordering JSON field types specifically, so hoping someone has run into this.
我有一个 Postgres 表，它有一个 JSON 类型的列，其中有一堆 JSON 对象。我想查询表记录并按存储在 JSON 字段中的值对结果进行排序。我正在运行查询，但它们没有正确排序。我没有找到大量关于专门订购 JSON 字段类型的文档，所以希望有人遇到过这个问题。
data: {name: "stuff", value: "third option"}
data: {name: "stuff", value: "awesome stuff"}
data: {name: "stuff", value: "way cooler stuff"}
The following SQL executes but the results return unordered
以下 SQL 执行但结果返回无序
select * from table ORDER BY data->>'value asc'
I'm using rails, but have tried running SQL directly as well w/ same result
我正在使用 Rails，但也尝试过直接运行 SQL 并得到相同的结果
回答by Craig Ringer
You put ascin the fieldname. There's no key named value ascin the json, so data ->> 'value asc'will always return NULL.
你asc输入字段名。value ascjson 中没有命名的键，因此data ->> 'value asc'将始终返回NULL.
You actually want:
你实际上想要：
select * from table ORDER BY data->>'value' ASC
to match the json, possibly even:
匹配json，甚至可能：
select *
from table
WHERE data ->> 'name' = 'stuff'
ORDER BY data->>'value' ASC
回答by acohen
Use ->instead of ->>(->>gets a JSON object field as text):
使用->代替->>(->>获取 JSON 对象字段作为文本)：
select * from my_table ORDER BY data->'some_number' asc;
回答by Marc
Try:
尝试：
ORDER BY cast(data->>'value' as integer) ASC
ORDER BY cast(data->>'value' as integer) ASC
