Output mode JSON should either be an Array or not use trailing commas

[fzakaria]

The json output mode emits trailing commas which makes it difficult to parse with many standard JSON tools like jq or python itself.

Here is me generating the list:

❯ sqlelf /usr/bin/ruby --sql ".mode json" --sql "select path,name from elf_sections;" > test.json

❯ head test.json
{ "path": "\/usr\/bin\/ruby", "name": ""},
{ "path": "\/usr\/bin\/ruby", "name": ".interp"},
{ "path": "\/usr\/bin\/ruby", "name": ".note.gnu.property"},
{ "path": "\/usr\/bin\/ruby", "name": ".note.gnu.build-id"},
{ "path": "\/usr\/bin\/ruby", "name": ".note.ABI-tag"},
{ "path": "\/usr\/bin\/ruby", "name": ".gnu.hash"},
{ "path": "\/usr\/bin\/ruby", "name": ".dynsym"},
{ "path": "\/usr\/bin\/ruby", "name": ".dynstr"},
{ "path": "\/usr\/bin\/ruby", "name": ".gnu.version"},
{ "path": "\/usr\/bin\/ruby", "name": ".gnu.version_r"},

Failing with JQ:

❯ cat test.json | jq
{
  "path": "/usr/bin/ruby",
  "name": ""
}
parse error: Expected value before ',' at line 1, column 42

Failing with Python:

❯ python
Python 3.10.12 (main, Jun  6 2023, 22:43:10) [GCC 12.2.0] on linux
Type "help", "copyright", "credits" or "license" for more information.
>>> import json
>>> f = open('test.json', 'r')
>>> data = json.load(f)
Traceback (most recent call last):
  File "<stdin>", line 1, in <module>
  File "/nix/store/bc45k1n0pkrdkr3xa6w84w1xhkl1kkyp-python3-3.10.12/lib/python3.10/json/__init__.py", line 293, in load
    return loads(fp.read(),
  File "/nix/store/bc45k1n0pkrdkr3xa6w84w1xhkl1kkyp-python3-3.10.12/lib/python3.10/json/__init__.py", line 346, in loads
    return _default_decoder.decode(s)
  File "/nix/store/bc45k1n0pkrdkr3xa6w84w1xhkl1kkyp-python3-3.10.12/lib/python3.10/json/decoder.py", line 340, in decode
    raise JSONDecodeError("Extra data", s, end)
json.decoder.JSONDecodeError: Extra data: line 1 column 42 (char 41)

I think this can be remedied by wrapping the row results in brackets to make them a list which would make it compliant.

[rogerbinns]

There is reason and it was reason from almost 20 years ago, when the SQLite shell didn't have JSON output and jsonl wasn't a thing yet. The reason back then was anticipating wanting to join the output of multiple queries which is why use JSON and not a different format, since it would allow each query result to have different columns, and then have manual [ / ] put around it. Anyway that reason is very bogus now.

The fix is:

• [x] Make mode json output the [ / ] around the results each time
• [x] Add mode jsonl which does newline delimited json

[fzakaria]

Sounds reasonable.