Skip to content

Instantly share code, notes, and snippets.

@groue
Created October 7, 2023 09:28
Show Gist options
  • Save groue/046ebddb4a410304094c022d756081c8 to your computer and use it in GitHub Desktop.
Save groue/046ebddb4a410304094c022d756081c8 to your computer and use it in GitHub Desktop.
Test JSON support
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)")
}
}
}
}
}
@martindufort
Copy link

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 ===

@martindufort
Copy link

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 ===

@martindufort
Copy link

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 ===

@martindufort
Copy link

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 ===

@martindufort
Copy link

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

Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment