按JSON数据类型postgres排序 [英] order by JSON data type postgres
问题描述
我有一个Postgres表,该表的列类型为JSON,其中包含一堆JSON对象.我想查询表记录并按JSON字段中存储的值对结果进行排序.我正在运行查询,但它们无法正确排序.我找不到大量有关专门订购JSON字段类型的文档,所以希望有人遇到了这个问题.
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.
data: {name: "stuff", value: "third option"}
data: {name: "stuff", value: "awesome stuff"}
data: {name: "stuff", value: "way cooler stuff"}
执行以下SQL,但结果返回无序
The following SQL executes but the results return unordered
select * from table ORDER BY data->>'value asc'
我正在使用rails,但是尝试过直接运行SQL并获得相同的结果
I'm using rails, but have tried running SQL directly as well w/ same result
推荐答案
您将asc
放在字段名称中. json中没有名为value asc
的键,因此data ->> 'value asc'
将始终返回NULL
.
You put asc
in the fieldname. There's no key named value asc
in the json, so data ->> 'value asc'
will always return NULL
.
您实际上要:
select * from table ORDER BY data->>'value' ASC
匹配json,甚至可能匹配:
to match the json, possibly even:
select *
from table
WHERE data ->> 'name' = 'stuff'
ORDER BY data->>'value' ASC
这篇关于按JSON数据类型postgres排序的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持IT屋!