order by JSON data type postgres

Question

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"} 

The following SQL executes but the results return unordered

select * from table ORDER BY data->>'value asc'  

I'm using rails, but have tried running SQL directly as well w/ same result

Answer 1

You put asc in the fieldname. There's no key named value asc in the json, so data ->> 'value asc' will always return NULL.

You actually want:

select * from table ORDER BY data->>'value' ASC  

to match the json, possibly even:

select *  from table  WHERE data ->> 'name' = 'stuff' ORDER BY data->>'value' ASC