Test JSON support

JSONSupportTests.swift

import XCTest
import GRDB

class JSONSupportTests: GRDBTestCase {
    func testJSONSupport() throws {
        let queries = [
            #"select json('[]') = '[]';"#,
            #"select json_array() = '[]';"#,
            #"select json_array_length('[]') = 0;"#,
            #"select json_array_length('[[]]','$[0]') = 0;"#,
            #"select json_extract('[0]','$[0]') = 0;"#,
            #"select '[0]' -> 0 = '0';"#,
            #"select '[0]' ->> 0 = 0;"#,
            #"select json_insert('[]','$[0]',0) = '[0]';"#,
            #"select json_object('a',0) = '{"a":0}';"#,
            #"select json_patch('[]','[]') = '[]';"#,
            #"select json_remove('[0]','$[0]') = '[]';"#,
            #"select json_replace('[1]','$[0]',0) = '[0]';"#,
            #"select json_set('[]','$[0]',0) = '[0]';"#,
            #"select json_type('[]') = 'array';"#,
            #"select json_type('[0]','$[0]') = 'integer';"#,
            #"select json_valid('[]') = 1;"#,
            #"select json_quote('[]') = '"[]"';"#,
            #"select json_group_array(a) = '[0]' from (select 0 AS a);"#,
            #"select json_group_object(key,value) = '{"a":0}' from (select 'a' AS key, 0 as value);"#,
        ]
        
        try DatabaseQueue().read { db in
            print("OS", ProcessInfo.processInfo.operatingSystemVersionString)
            try print("SQLite version:",  String.fetchOne(db, sql: "select sqlite_version()") ?? "unknown")
            
            for query in queries {
                do {
                    let result = try Bool.fetchOne(db, sql: query)
                    XCTAssert(result == true, "Unexpected result executing \(query)")
                } catch {
                    XCTFail("Unexpected error executing \(query): \(error)")
                }
            }
        }
    }
}

OS: Version 13.5.2 (Build 22G91)
SQLite version: 3.39.5
--- PASSED: select json('[]') = '[]';
--- PASSED: select json_array() = '[]';
--- PASSED: select json_array_length('[]') = 0;
--- PASSED: select json_array_length('[[]]','$[0]') = 0;
--- PASSED: select json_extract('[0]','$[0]') = 0;
--- PASSED: select '[0]' -> 0 = '0';
--- PASSED: select '[0]' ->> 0 = 0;
--- PASSED: select json_insert('[]','$[0]',0) = '[0]';
--- PASSED: select json_object('a',0) = '{"a":0}';
--- PASSED: select json_patch('[]','[]') = '[]';
--- PASSED: select json_remove('[0]','$[0]') = '[]';
--- PASSED: select json_replace('[1]','$[0]',0) = '[0]';
--- PASSED: select json_set('[]','$[0]',0) = '[0]';
--- PASSED: select json_type('[]') = 'array';
--- PASSED: select json_type('[0]','$[0]') = 'integer';
--- PASSED: select json_valid('[]') = 1;
--- PASSED: select json_quote('[]') = '"[]"';
--- PASSED: select json_group_array(a) = '[0]' from (select 0 AS a);
--- PASSED: select json_group_object(key,value) = '{"a":0}' from (select 'a' AS key, 0 as value);
=== ALL TESTS PASSED ===

OS: Version 11.6 (Build 20G165)
SQLite version: 3.32.3
--- PASSED: select json('[]') = '[]';
--- PASSED: select json_array() = '[]';
--- PASSED: select json_array_length('[]') = 0;
--- PASSED: select json_array_length('[[]]','$[0]') = 0;
--- PASSED: select json_extract('[0]','$[0]') = 0;
*** ERROR: Unexpected error executing select '[0]' -> 0 = '0';: SQLite error 1: near ">": syntax error - while executing `select '[0]' -> 0 = '0'`
*** ERROR: Unexpected error executing select '[0]' ->> 0 = 0;: SQLite error 1: near ">>": syntax error - while executing `select '[0]' ->> 0 = 0`
--- PASSED: select json_insert('[]','$[0]',0) = '[0]';
--- PASSED: select json_object('a',0) = '{"a":0}';
--- PASSED: select json_patch('[]','[]') = '[]';
--- PASSED: select json_remove('[0]','$[0]') = '[]';
--- PASSED: select json_replace('[1]','$[0]',0) = '[0]';
--- PASSED: select json_set('[]','$[0]',0) = '[0]';
--- PASSED: select json_type('[]') = 'array';
--- PASSED: select json_type('[0]','$[0]') = 'integer';
--- PASSED: select json_valid('[]') = 1;
--- PASSED: select json_quote('[]') = '"[]"';
--- PASSED: select json_group_array(a) = '[0]' from (select 0 AS a);
--- PASSED: select json_group_object(key,value) = '{"a":0}' from (select 'a' AS key, 0 as value);
=== 2 ERRORS DETECTED ===

OS: Version 12.6.2 (Build 21G320)
SQLite version: 3.37.0
--- PASSED: select json('[]') = '[]';
--- PASSED: select json_array() = '[]';
--- PASSED: select json_array_length('[]') = 0;
--- PASSED: select json_array_length('[[]]','$[0]') = 0;
--- PASSED: select json_extract('[0]','$[0]') = 0;
*** ERROR: Unexpected error executing select '[0]' -> 0 = '0';: SQLite error 1: near ">": syntax error - while executing `select '[0]' -> 0 = '0'`
*** ERROR: Unexpected error executing select '[0]' ->> 0 = 0;: SQLite error 1: near ">>": syntax error - while executing `select '[0]' ->> 0 = 0`
--- PASSED: select json_insert('[]','$[0]',0) = '[0]';
--- PASSED: select json_object('a',0) = '{"a":0}';
--- PASSED: select json_patch('[]','[]') = '[]';
--- PASSED: select json_remove('[0]','$[0]') = '[]';
--- PASSED: select json_replace('[1]','$[0]',0) = '[0]';
--- PASSED: select json_set('[]','$[0]',0) = '[0]';
--- PASSED: select json_type('[]') = 'array';
--- PASSED: select json_type('[0]','$[0]') = 'integer';
--- PASSED: select json_valid('[]') = 1;
--- PASSED: select json_quote('[]') = '"[]"';
--- PASSED: select json_group_array(a) = '[0]' from (select 0 AS a);
--- PASSED: select json_group_object(key,value) = '{"a":0}' from (select 'a' AS key, 0 as value);
=== 2 ERRORS DETECTED ===

OS: Version 10.15.7 (Build 19H15)
SQLite version: 3.28.0
--- PASSED: select json('[]') = '[]';
--- PASSED: select json_array() = '[]';
--- PASSED: select json_array_length('[]') = 0;
--- PASSED: select json_array_length('[[]]','$[0]') = 0;
--- PASSED: select json_extract('[0]','$[0]') = 0;
*** ERROR: Unexpected error executing select '[0]' -> 0 = '0';: SQLite error 1: near ">": syntax error - while executing `select '[0]' -> 0 = '0'`
*** ERROR: Unexpected error executing select '[0]' ->> 0 = 0;: SQLite error 1: near ">>": syntax error - while executing `select '[0]' ->> 0 = 0`
--- PASSED: select json_insert('[]','$[0]',0) = '[0]';
--- PASSED: select json_object('a',0) = '{"a":0}';
--- PASSED: select json_patch('[]','[]') = '[]';
--- PASSED: select json_remove('[0]','$[0]') = '[]';
--- PASSED: select json_replace('[1]','$[0]',0) = '[0]';
--- PASSED: select json_set('[]','$[0]',0) = '[0]';
--- PASSED: select json_type('[]') = 'array';
--- PASSED: select json_type('[0]','$[0]') = 'integer';
--- PASSED: select json_valid('[]') = 1;
--- PASSED: select json_quote('[]') = '"[]"';
--- PASSED: select json_group_array(a) = '[0]' from (select 0 AS a);
--- PASSED: select json_group_object(key,value) = '{"a":0}' from (select 'a' AS key, 0 as value);
=== 2 ERRORS DETECTED ===

Here's the DMG for the macOS version if you want to run it locally.
It is notarized.

https://storage.googleapis.com/sqlite-validator/SQLiteJSONValidator.dmg

Cheers - Martin