How do I ORDER BY a JSON Array with its String Values?

Question

I have a simple JSON array:

_bb jsonb =

[
  {
    "so": "1",
    "mdl": "Testing",
    "pos": "Top"
  },
  {
    "so": "1",
    "mdl": "FlashSale",
    "pos": "Bottom"
  },
  {
    "so": "1",
    "mdl": "Testing",
    "pos": "Top"
  }
]

What I want to achieve:

[
  {
    "so": "1",
    "mdl": "Testing",
    "pos": "Top"
  },
  {
    "so": "1",
    "mdl": "Testing",
    "pos": "Top"
  },
  {
    "so": "1",
    "mdl": "FlashSale",
    "pos": "Bottom"
  }
]

I tried doing

Select _bb  into _test  ORDER BY _bb->>'pos' ASC  ;

What I achieve is all the pos = "Top" be the first in the JSON and all the "Bottom" to be last. Is there a way to achieve this in Postgres?

I dont understand how ORDER BY _bb->>'pos' ASC would help to change the array index - can you share the structure?.. also in your original jsonb, _bb[1] is equal to _bb[3] - right?.. so what you say you have achieved is what you want then - no? — Vao Tsun
– Vao Tsun, Commented Nov 2, 2017 at 9:21
stackoverflow.com/questions/4088532/custom-order-by-explanation This should do the trick. You just have to convert it to your case — Doomenik
– Doomenik, Commented Nov 2, 2017 at 9:22
@VaoTsun Yea its _bb[3], but i would like to change the sort orders by placing "Top" as the priority to be first before "bottom". I'm not really too sure on what the syntax is to achieve it. — Pickles
– Pickles, Commented Nov 2, 2017 at 9:23
alphabetically "bottom" is before "top" - are those two possible values or there can be more?.. — Vao Tsun
– Vao Tsun, Commented Nov 2, 2017 at 9:25

Vao Tsun · Accepted Answer · 2017-11-02 10:34:26Z

3

with j(_bb) as (values('[
  {
    "so": "1",
    "mdl": "Testing",
    "pos": "Top"
  },
  {
    "so": "1",
    "mdl": "FlashSale",
    "pos": "Bottom"
  },
  {
    "so": "1",
    "mdl": "Testing",
    "pos": "Top"
  }
]'::jsonb))
, ord(o,v) as (values(1,'Top'),(2,'Bottom'))
, un as (select *,jsonb_array_elements(_bb) i from j)
select jsonb_agg(i order by o) from un
join ord on v=i->>'pos'
;

with result in:

[
  {
    "so": "1",
    "mdl": "Testing",
    "pos": "Top"
  },
  {
    "so": "1",
    "mdl": "Testing",
    "pos": "Top"
  },
  {
    "so": "1",
    "mdl": "FlashSale",
    "pos": "Bottom"
  }
]

needless to say you have to build order matrix for all values, eg:

, ord(o,v) as (values(1,'Top'),(2,'Middle'),(3,'Bottom'))

http://rextester.com/ZNDQ97753

edited Nov 2, 2017 at 10:34

answered Nov 2, 2017 at 9:35

Vao Tsun

52.4k13 gold badges114 silver badges149 bronze badges

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

5 Comments

Pickles Over a year ago

Thank, but can you explain the first line " t=# with " im not too familiar with this syntax.

Vao Tsun Over a year ago

ah - t=# is just a psql prompt, with is CTE syntax

Pickles Over a year ago

noob question but, who do I test this out in a Query? I'm getting a syntax error "at or near t" when I do.

Vao Tsun Over a year ago

@Pickles I updated the answer, leaving query with no psql prompts or other decortions

Vao Tsun Over a year ago

and added link to live demo

Collectives™ on Stack Overflow

How do I ORDER BY a JSON Array with its String Values?

1 Answer 1

5 Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

5 Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related