I love SQLite's quality and their docs explaining this kind of things. However, not all parts of SQLite have the same level of quality. I was very disappointed when I found bugs related to its JSON functions (and several other similar bugs related to other features):
SQLite supports a set of JSON functions that let you query and index JSON columns directly, which looks very convenient—but be careful:
1. `json('{"a/b": 1}') != json('{"a\/b": 1}')`
Although the two objects are identical in terms of JSON semantics, SQLite treats them as different.
2. `json_extract('{"a\/b": 1}', '$.a/b') is null`, `json_extract('{"\u0031":1}', '$.1') is null`, `json_extract('{"\u6211":1}', '$.我') is null`
This issue only exists in older versions of SQLite; the latest versions have fixed it.
In many cases you can't control how your JSON library escapes characters. For example, `/` doesn’t need to be escaped, but some libraries will escape it as `\/`. So this is a rather nasty pitfall, you can end up failing to match keys during extraction with seemingly no reason.
SQLite supports a set of JSON functions that let you query and index JSON columns directly, which looks very convenient—but be careful:
1. `json('{"a/b": 1}') != json('{"a\/b": 1}')`
Although the two objects are identical in terms of JSON semantics, SQLite treats them as different.
2. `json_extract('{"a\/b": 1}', '$.a/b') is null`, `json_extract('{"\u0031":1}', '$.1') is null`, `json_extract('{"\u6211":1}', '$.我') is null`
This issue only exists in older versions of SQLite; the latest versions have fixed it.
In many cases you can't control how your JSON library escapes characters. For example, `/` doesn’t need to be escaped, but some libraries will escape it as `\/`. So this is a rather nasty pitfall, you can end up failing to match keys during extraction with seemingly no reason.