JSONB Has Landed in SQLite

Re:JSONB Has Landed in SQLite by kevincox on Wednesday December 06, 2023 @11:03AM (#38545655)

Slight caveat. It seems that you should default to JSONB for all cases except for values directly returned in the query result. As IIUC you will get a JSONB blob back and will be responsible for parsing it on the client.

So use it for writes:

    UPDATE t SET col = jsonb_*(?)

Also use it for filters if applicable (although this seems like a niche use case, I can't actually think of an example).

But if returning values you probably want to use `json_*` functions

    SELECT json_extract(col1, '$.myProp') FROM t

Otherwise you will end up receiving the binary format.

[ Parent | on HN ]

Re:JSONB Has Landed in SQLite by yencabulator on Wednesday December 06, 2023 @07:35PM (#38551409)

Wait is it really true that if you use SQLite's JSONB format, `select my_json_column from foo` becomes unreadable to humans? That seems.. unacceptable. One would expect it to convert its internal format back to JSON for consumption.

[ Parent | on HN ]