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?
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'))