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

Chris Marisic · Accepted Answer · 2017-05-01 19:51:59Z

89

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

edited May 1, 2017 at 19:51

Chris Marisic

33.2k30 gold badges169 silver badges264 bronze badges

answered Sep 22, 2014 at 6:37

Craig Ringer

329k83 gold badges742 silver badges820 bronze badges

Sign up to request clarification or add additional context in comments.

6 Comments

user1767105 Over a year ago

I had tried that too. it runs, but still doesn't order the records correctly. (by the values for stuff) do i need to cast the value or something?

Craig Ringer Over a year ago

@user1767105 See update. You're trying to get a non-existent key 'stuff', so it'll be equivalent to ORDER BY NULL.

user1767105 Over a year ago

got it. sorry i had the data objects incorrect above. really it was data: {"stuff" => {"name" => "stuff", "value": "awesome stuff"}} query ends up as select....order by data->"stuff"->>"value" your help got me there though so thanks! additionally a good resource as well: clarkdave.net/2013/06/what-can-you-do-with-postgresql-and-json

Natim Over a year ago

How is this supposed to work with integer? They seems to be sorted as if they were string: 1, 10, 46, 6 rather than 1, 6, 10, 46

Craig Ringer Over a year ago

@Natim Please post a new, separate question and link to this one for context. Include complete examples.

|

okrunner · Accepted Answer · 2017-12-15 16:57:15Z

39

Use -> instead of ->> (->> gets a JSON object field as text):

select * from my_table ORDER BY data->'some_number' asc;

answered Dec 15, 2017 at 16:57

okrunner

3,20332 silver badges24 bronze badges

1 Comment

datashaman Over a year ago

While this does directly answer the question, I am grateful I found this. It helped me to solve a JSON column being sorted alphabetically when it is a number.

Chris Marisic · Accepted Answer · 2017-05-01 19:47:21Z

20

Try:

ORDER BY cast(data->>'value' as integer) ASC

edited May 1, 2017 at 19:47

Chris Marisic

33.2k30 gold badges169 silver badges264 bronze badges

answered May 12, 2016 at 8:50

Marc

2633 silver badges10 bronze badges

1 Comment

Chris Marisic Over a year ago

@EugenKonkov fixed the question & answers

Collectives™ on Stack Overflow

order by JSON data type postgres

3 Answers 3

6 Comments

1 Comment

1 Comment

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

6 Comments

1 Comment

1 Comment

Your Answer

Sign up or log in

Post as a guest

Linked

Related