SQLite Forum

Timeline
Login

9 forum posts by user bokwoon

2022-01-10
11:33 Reply: Proposed JSON enhancements. (artifact: 2280137ad8 user: bokwoon)

Personally I think that Postgres' -> and ->> syntax was a mistake, it confuses too many people because ->'a'->'b'->>'c' is unnatural compared to .a.b.c. In that sense SQLite and MySQL's choice of using json_extract was far more pragmatic, because JSON path resembles something that people know. Postgres 12 added proper JSON path support, and Postgres 14 added new JSON subscripting operators to make JSON access even more familiar (you can see people complaining about the old Postgres -> and ->> syntax in these threads https://news.ycombinator.com/item?id=27247621 https://www.reddit.com/r/PostgreSQL/comments/nq0p1k/better_json_in_postgres_with_postgresql_14/).

The biggest benefit to adding the new -> and ->> operators is that existing Postgres or MySQL JSON queries will just work without modification in SQLite, but that is not always the case due to the semantic mismatch between ->/->> in MySQL/Postgres and json_nextract/json_extract in SQLite. What about decoupling -> and ->> from json_nextract and json_extract and directly emulating Postgres and MySQL behaviour? Something like

sqlite> SELECT '{"a":{"b":{"c":"value"}}}'->'a'->'b'->'c';
"value"

sqlite> SELECT '{"a":{"b":{"c":"value"}}}'->'a'->'b'->>'c';
value

sqlite> SELECT '{"a":{"b":{"c":"value"}}}'->'$.a.b.c';
"value"

sqlite> SELECT '{"a":{"b":{"c":"value"}}}'->>'$.a.b.c';
value

sqlite> SELECT json_extract('{"a":{"b":{"c":"value"}}}', '$.a.b.c');
value

sqlite> SELECT json_extract('{"a":{"b":{"c":"value', '$.a.b.c');
malformed JSON

sqlite> SELECT json_nextract('{"a":{"b":{"c":"value', '$.a.b.c');
NULL
10:42 Edit reply: Proposed JSON enhancements. (artifact: 0122b61802 user: bokwoon)

Oh my error example for Postgres/SQLite is wrong, feeding NULL from json_nextract into json_extract should also return NULL. So in order for SQLite to behave similarly to Postgres for invalid inputs, you must use ->> all the way i.e.

->>'a'->>'b'->>'c' (not valid in postgres)

instead of

->'a'->'b'->>'c'
10:38 Edit reply: Proposed JSON enhancements. (artifact: 5e4b2630c4 user: bokwoon)

Oh my error example for Postgres/SQLite is wrong, feeding NULL from json_nextract into json_extract should also return NULL. So for invalid inputs, ->'a'->'b'->>'c' behaves differently for SQLite unless ->>'a'->>'b'->>'c' is used (which is invalid Postgres syntax).

10:36 Edit reply: Proposed JSON enhancements. (artifact: 0325529caf user: bokwoon)

Oh my error example for Postgres/SQLite are wrong, feeding NULL from json_nextract into json_extract should also be NULL. So SELECT '{"a":{"b":{"c":"value'->'a'->'b'->>'c'; will return NULL as well?

09:16 Reply: Proposed JSON enhancements. (artifact: 276123bbe9 user: bokwoon)

Oh my error example for Postgres/SQLite are wrong, feeding NULL from json_nextract into json_extract should also be NULL. So SELECT '{"a":{"b":{"c":"value"}}}'->'a'->'b'->>'c'; will return NULL as well?

09:07 Reply: Proposed JSON enhancements. (artifact: a0d681bb24 user: bokwoon)

I think it is safer to mention that the -> operator is incompatible with MySQL and Postgres' version of ->. In MySQL and Postgres, the only reason why you would use -> is to get a JSON quoted string, which no SQLite json function supports. Instead, it is only when using the ->> operator that behaviour between SQLite and MySQL or SQLite and Postgres are semantically identical.

# Postgres and SQLite

-- for valid inputs, '->' is not compatible
postgres> SELECT '{"a":{"b":{"c":"value"}}}'::JSON->'a'->'b'->'c';
"value"
sqlite> SELECT '{"a":{"b":{"c":"value"}}}'->'a'->'b'->'c';
value

-- for valid inputs, '->>' is compatible
postgres> SELECT '{"a":{"b":{"c":"value"}}}'::JSON->'a'->'b'->>'c';
value
sqlite> SELECT '{"a":{"b":{"c":"value"}}}'->'a'->'b'->>'c';
value

-- for invalid inputs, '->' is not compatible
postgres> SELECT '{"a":{"b":{"c":"value'::JSON->'a'->'b'->'c';
ERROR:  invalid input syntax for type json
sqlite> SELECT '{"a":{"b":{"c":"value'->'a'->'b'->'c'; -- json_nextract cascades NULL all the way down the chain
NULL

-- for invalid inputs, '->>' is compatible
postgres> SELECT '{"a":{"b":{"c":"value'::JSON->'a'->'b'->>'c';
ERROR:  invalid input syntax for type json
sqlite> SELECT '{"a":{"b":{"c":"value'->'a'->'b'->>'c'; -- json_nextract cascades NULL, but gets caught by the json_extract at the end of the chain
malformed JSON
# MySQL and SQLite
# I'm using json_extract() and json_unquote(json_extract()) as aliases for -> and ->> because no db fiddle website I found seems to support that syntax for MySQL

-- for valid inputs, '->' is not compatible
mysql> SELECT json_extract('{"a":{"b":{"c":"value"}}}', '$.a.b.c');
"value"
sqlite> SELECT '{"a":{"b":{"c":"value"}}}'->'$.a.b.c';
value

-- for valid inputs, '->>' is compatible
mysql> SELECT json_unquote(json_extract('{"a":{"b":{"c":"value"}}}', '$.a.b.c'));
value
sqlite> SELECT '{"a":{"b":{"c":"value"}}}'->>'$.a.b.c';
value

-- for invalid inputs, '->' is not compatible
mysql> SELECT json_extract('{"a":{"b":{"c":"value', '$.a.b.c');
Error: ER_INVALID_JSON_TEXT_IN_PARAM
sqlite> SELECT '{"a":{"b":{"c":"value'->'$.a.b.c'; -- json_nextract cascades NULL all the way down the chain
NULL

-- for invalid inputs, '->>' is compatible
mysql> SELECT json_unquote(json_extract('{"a":{"b":{"c":"value', '$.a.b.c'));
Error: ER_INVALID_JSON_TEXT_IN_PARAM
sqlite> SELECT '{"a":{"b":{"c":"value'->>'$.a.b.c'; -- json_extract catches the error
malformed JSON
2021-09-30
03:36 Edit: Feature request: sqlite CLI allow `.mode line` and `.mode quote` together (artifact: 249e7cb706 user: bokwoon)

I use sqlite CLI's .mode line output format as my default, however it does not show blobs quoted as hexadecimals like .mode quote does. But if I use .mode quote I lose the ability to display each column is a separate line. Is it possible to enable both line and quote behaviours together? i.e. each column appears in its own line, but the value is quoted accordingly.

My use case is UUID values, where I have opted to use BLOB over TEXT because of the space efficiency. UUID values are not displayable in .mode line output format at all.

sqlite> .schema
CREATE TABLE sites (
    site_id BLOB,
    domain TEXT,
    subdomain TEXT
);
sqlite> .mode line
sqlite> SELECT * FROM sites;
  site_id =
   domain = 𝗡𝗨𝗟𝗟
subdomain = 𝗡𝗨𝗟𝗟
sqlite> .mode quote
sqlite> SELECT * FROM sites;
'site_id','domain','subdomain'
X'747fd11e07fa4cc18279091f257c6292',NULL,NULL

What I would prefer:

sqlite> .mode line quote
sqlite> SELECT * FROM sites;
  site_id = X'747fd11e07fa4cc18279091f257c6292'
   domain = NULL
subdomain = NULL
03:34 Post: Feature request: sqlite CLI allow `.mode line` and `.mode quote` together (artifact: d6023e47a1 user: bokwoon)

I use sqlite CLI's .mode line output format as my default, however it does not show blobs quoted as hexadecimals like .mode quote does. But if I use .mode quote I lose the ability to display each column is a separate line. Is it possible to enable both line and quote behaviours together? i.e. each column appears in its own line, but the value is quoted accordingly.

My use case is UUID values, where I have opted to use BLOB over TEXT because of the space efficiency. UUID values are not displayable in .mode line output format at all.

sqlite> .schema
CREATE TABLE sites (
    site_id BLOB,
    domain TEXT,
    subdomain TEXT
);
sqlite> .mode line
sqlite> SELECT * FROM sites;
  site_id =
   domain = 𝗡𝗨𝗟𝗟
subdomain = 𝗡𝗨𝗟𝗟
sqlite> .mode quote
sqlite> SELECT * FROM sites;
'site_id','domain','subdomain'
X'747fd11e-07fa-4cc1-8279-091f257c6292',NULL,NULL

What I would prefer:

sqlite> .mode line quote
sqlite> SELECT * FROM sites;
  site_id = X'747fd11e-07fa-4cc1-8279-091f257c6292'
   domain = NULL
subdomain = NULL
2021-09-24
07:29 Reply: How to find out whether a table is STRICT? (artifact: 03a7eae886 user: bokwoon)

Awesome, I've been using an ugly hack to filter out shadow tables during db introspection: SELECT * FROM sqlite_schema WHERE type = 'table' AND sql NOT LIKE 'CREATE TABLE ''%' (since I noticed all FTS5 shadow tables started with single quotes). With this pragma_table_list I can join on the tables that are actually user-created tables.