json_insert differs in resultset 3.39.0 when using a view

(1) By anthony (aaplansbergen) on 2024-11-12 10:40:01 [link] [source]

Hi, I have a different result in my query since I updated to the latest version. After some research I found out that in version 3.39.0 the result gets different.

In a nuttshell:


CREATE VIEW demo_view AS SELECT  json_object('a','b') AS json;
SELECT json_insert(json('[]'),'$[#]', (SELECT  json FROM demo_view));

in version 3.38.5: [{"a":"b"}]
in version 3.39.0: ["{"a":"b"}"]

I know how to fix it, but was my code wrong, or is there a bug introduces in version 3.39?

(2) By Dan Kennedy (dan) on 2024-11-12 11:12:37 in reply to 1 [source]

It's intentional. Subtypes are not supposed to cross sub-query boundaries. I'm not sure where (if) that's documented, but here's the change and the forum thread that prompted it:

https://sqlite.org/src/info/bbaf1f2eb1e1637b

https://sqlite.org/forum/forumpost/3d9caa45cbe38c78

Dan.

(4) By anthony (aaplansbergen) on 2024-11-12 11:21:54 in reply to 2 [link] [source]

thanks for the quick response

(3) By Richard Hipp (drh) on 2024-11-12 11:13:15 in reply to 1 [link] [source]

The change in behavior is not a bug but rather a bug fix, specifically the fix for the bug identified at Forum post 3d9caa45cbe38c78.

The value returned by your VIEW is not JSON. It is a text string. When you JSON_INSERT a text string, it gets inserted as a text string, even if it just happens to look like JSON.

(5) By anthony (aaplansbergen) on 2024-11-12 11:23:31 in reply to 3 [link] [source]

thanks for the quick response, I will fix my query using the json(X) function