Search code examples
sqljsonoracle-databaseoracle19c

Order By not working when using JSON_ARRAYAGG

If I do something like:

SELECT name
FROM customers
ORDER BY name

They come out in alphabetical order.

But if I do:

SELECT JSON_ARRAYAGG(name)
FROM customers
ORDER BY name

they come out in the same order every time, but its not alphabetical

Is this a bug, or do I need to do something different?

Solution

  • Your ORDER BY needs to be within the JSON_ARRAYAGG function.

    SELECT JSON_ARRAYAGG(name ORDER BY name)
FROM customers

    Having the ORDER BY within the JSON_ARRAYAGG function sorts the values within the JSON array. Having the ORDER BY at the end of your query sorts the order the rows are returned.