SQLITE json_group_array ordering

Question

I am trying to get a nested JSON array in SQLite using the following query:

SELECT json_group_array( 
        json_object(
            'trip_id', t.id, 
            'trip_desc', t.desc,
            'trip_files', (SELECT json_group_array(json_object('file_id', f.id, 'file_desc', f.desc))
                    FROM files f
                    WHERE f.trip = t.id ORDER BY f.id ASC)
        )
    )
FROM trips t
WHERE t.year IN (SELECT id FROM years WHERE id=1)
ORDER BY t.id ASC;

Giving the following output:

[
    {
        "trip_id": 2,
        "trip_desc": "trip2",
        "trip_files": [
            {
                "file_id": 3,
                "file_desc": "file3"
            },
            {
                "file_id": 4,
                "file_desc": "file4"
            }
        ]
    },
    {
        "trip_id": 1,
        "trip_desc": "trip1",
        "trip_files": [
            {
                "file_id": 2,
                "file_desc": "file2"
            },
            {
                "file_id": 1,
                "file_desc": "file1"
            }
        ]
    }
]

As you can notice, the ORDER BY clause is being ignored. You can see the issue also in the following DBFiddle:

https://www.db-fiddle.com/f/bnjzbLUUoQFWAinMdQqovG/0

Any ideas on how to accomplish the ordering? Thanks!

Answer 1

With aggregate functions like json_group_array(), "If no ORDER BY clause is specified, the inputs to the aggregate occur in an arbitrary order that might change from one invocation to the next." Support for ORDER BY in an aggregate function was added in sqlite 3.44 (more recent than the version that db-fiddle is using (3.39 as of this writing)). The SELECT's ORDER BY is irrelevant; it's the one in the aggregate function call that matters.

So you need a query like

SELECT json_group_array(
        json_object(
            'trip_id', t.id,
            'trip_desc', t.desc,
            'trip_files', (SELECT json_group_array(json_object('file_id', f.id, 'file_desc', f.desc) ORDER BY f.id)
                    FROM files f
                    WHERE f.trip = t.id)
        ) ORDER BY t.id)
FROM trips t
WHERE t.year IN (SELECT id FROM years WHERE id=1);

which gives (After running through a JSON formatter):

[
  {
    "trip_id": 1,
    "trip_desc": "trip1",
    "trip_files": [
      {
        "file_id": 1,
        "file_desc": "file1"
      },
      {
        "file_id": 2,
        "file_desc": "file2"
      }
    ]
  },
  {
    "trip_id": 2,
    "trip_desc": "trip2",
    "trip_files": [
      {
        "file_id": 3,
        "file_desc": "file3"
      },
      {
        "file_id": 4,
        "file_desc": "file4"
      }
    ]
  }
]

---

I noticed in testing an answer that with sqlite 3.44, json_group_array(json expression ORDER BY expression) converts the json expression part to a JSON string even if it starts out as some other type like an array or object, making it pretty much unusable. Sqlite 3.45, which features a complete overhaul of its JSON support, produces the desired results and is what was used to generate the above.