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, Nov 02 '17 at 09:21
https://stackoverflow.com/questions/4088532/custom-order-by-explanation This should do the trick. You just have to convert it to your case — Doomenik, Nov 02 '17 at 09: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, Nov 02 '17 at 09:23
alphabetically "bottom" is before "top" - are those two possible values or there can be more?.. — Vao Tsun, Nov 02 '17 at 09:25

Vao Tsun · Accepted Answer · 2017-11-02T10:34:26.727

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 02 '17 at 10:34

answered Nov 02 '17 at 09:35

Vao Tsun

47,234
13
100
132

Thank, but can you explain the first line " t=# with " im not too familiar with this syntax. – Pickles Nov 02 '17 at 10:06
ah - `t=# ` is just a `psql` prompt, `with` is `CTE` syntax – Vao Tsun Nov 02 '17 at 10:13
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. – Pickles Nov 02 '17 at 10:26
@Pickles I updated the answer, leaving query with no psql prompts or other decortions – Vao Tsun Nov 02 '17 at 10:29
and added link to live demo – Vao Tsun Nov 02 '17 at 10:34

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

1 Answers1