Sorting jsonb in postgresql

Question

I have a jsonb column in postgresql in which data are stored as

CREATE TABLE foo(response jsonb);
INSERT INTO foo VALUES
('[{"qs":"field1", "ans":"a"},{"qs":"field2", "ans":"1"}]' :: jsonb),
('[{"qs": "field1", "ans": "d"},{"qs": "field2", "ans": "4"}]' :: jsonb),
('[{"qs": "field1", "ans": "b"},{"qs": "field2", "ans": "3"}]' :: jsonb),
('[{"qs": "field1", "ans": "e"},{"qs": "field2", "ans": "2"}]' :: jsonb)

I need to sort key 'ans' value based on value 'field1' to generate result,

[{"qs": "field1", "ans": "a"}, {"qs": "field2", "ans": "1"}]
[{"qs": "field1", "ans": "b"}, {"qs": "field2", "ans": "3"}]
[{"qs": "field1", "ans": "d"}, {"qs": "field2", "ans": "4"}]
[{"qs": "field1", "ans": "e"}, {"qs": "field2", "ans": "2"}]


field1 | field2
  a    |    1
  b    |    3
  d    |    4
  e    |    2

also sorting based on 'field2',

[{"qs": "field1", "ans": "a"}, {"qs": "field2", "ans": "1"}]
[{"qs": "field1", "ans": "e"}, {"qs": "field2", "ans": "2"}]
[{"qs": "field1", "ans": "b"}, {"qs": "field2", "ans": "3"}]
[{"qs": "field1", "ans": "d"}, {"qs": "field2", "ans": "4"}]

field1 | field2
  a    |    1
  e    |    2
  b    |    3
  d    |    4

is there anyway to achieve this in postgresql???

S-Man · Accepted Answer · 2019-09-19 11:24:10Z

2

demo:db<>fiddle

I am not sure if you want:

A) sort the JSON objects as they are:

| response                                                     |
| :----------------------------------------------------------- |
| [{"qs": "field1", "ans": "a"}, {"qs": "field2", "ans": "1"}] |
| [{"qs": "field1", "ans": "b"}, {"qs": "field2", "ans": "3"}] |
| [{"qs": "field1", "ans": "d"}, {"qs": "field2", "ans": "4"}] |
| [{"qs": "field1", "ans": "e"}, {"qs": "field2", "ans": "2"}] |

Query for sorting json output:

SELECT
    *
FROM foo
ORDER BY response -> 0 -> 'ans'

Simply query the ans value within ORDER BY clause. If your want to sort by field2, you need to change the 0 into 1 (and maybe cast it into type int: ORDER BY (response -> 1 ->> 'ans')::int)

or B) You want to sort simply the ans values:

field1 | field2
:----- | -----:
a      |      1
b      |      3
d      |      4
e      |      2

Query for sorting ans values:

SELECT 
    response -> 0 ->> 'ans' AS field1,
    (response -> 1 ->> 'ans')::int AS field2
FROM
    foo
ORDER BY 1

Create the two columns from parsing the array. field1 is the first element of the JSON array, field2 the second one (indexes 0 and 1). After that you can order these columns ordinarily.

edited Sep 19, 2019 at 11:24

answered Sep 19, 2019 at 11:12

S-Man

24k9 gold badges51 silver badges78 bronze badges

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

5 Comments

Kamal Lama Over a year ago

can we sort 'ans' of json objects based on value of key 'qs'(eg:- 'field1' or 'field2') instead of using indexes 0 or 1 as you suggested in answer (A)

S-Man Over a year ago

Yes this is possible but more. complicated (and less fast) because in that case you need to expand the array elements to query the qs values first...

Kamal Lama Over a year ago

thanks for the answer, could you please include that in answer so that I can compare performance between those query, one with using indexes(0 or 1) and another with the value of key('field1' or 'field2')

S-Man Over a year ago

dbfiddle.uk/… This is really not very performant code, but it is working without any indexes. First: Expanding array to be able to query the element values by name. After that reaggregating them using a pivot mechanism with the FILTER clause.

Rei Miyasaka Over a year ago

Will this make use of any indexes if you do an order by?

Collectives™ on Stack Overflow

Sorting jsonb in postgresql

1 Answer 1

5 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

5 Comments

Your Answer

Sign up or log in

Post as a guest

Related