按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屋！