How to sort a PostgreSQL JSON field

Question

I have some table with json field. I want to group & count that field to understand its usage frequency. But the data in this field is stored in a random order, so I can get the wrong result. That's why I've decided to sort that field before grouping, but I failed. How can I do it?

JSON_FIELD
["one", "two"]
["two"]
["two", "one"]
["two"]
["three"]

I've tried this code:

SELECT JSON_FIELD, COUNT(1) FROM my_table GROUP BY JSON_FIELD;

But the result w/o sorting is wrong:

JSON_FIELD     COUNT
["one", "two"] 1
["two"]        2
["two", "one"] 1
["three"]      1

But if I could sort it somehow, the expected (and correct) result would be:

JSON_FIELD     COUNT
["one", "two"] 2
["two"]        2
["three"]      1

My question is very familiar to How to convert json array into postgres int array in postgres 9.3

score 0 · Answer 1 · answered Aug 15 '18 at 09:23

A bit messy but works:

SELECT ja::TEXT::JSON, COUNT(*)
FROM (
        SELECT JSON_AGG(je ORDER BY je::TEXT) AS ja
        FROM (
                SELECT JSON_ARRAY_ELEMENTS(j) je, ROW_NUMBER() OVER () AS r
                FROM (
                        VALUES
                        ('["one", "two"]'::JSON),
                        ('["two"]'),
                        ('["two", "one"]'),
                        ('["two"]'),
                        ('["three"]')
                ) v(j)
        ) el
        GROUP BY r
) x
GROUP BY ja::TEXT

Result:

BTW the casting of JSON values to TEXT is because (at least in PG 9.3) there are no JSON equality operators, so I cast to TEXT in order to be able to do GROUP or ORDER BY, then back to JSON.

How to sort a PostgreSQL JSON field

1 Answers1