Hi,

The affinity of JSON is not guaranteed under the current SQLite implementation, this is a request to have some form of support for it.

The typical case I have encountered is when returning a sorted array as json, the json_group_array will then operates on the result of a sorted subquery.

However right now, that pattern is not supported, for instance

select json_group_array(o) from (select json_object("hello",123) o);

will return

["{\"hello\":123}"]

and to obtain a more desirable JSON array you have to do

select json_group_array(json(o)) from (select json_object("hello",123) o)

which means there is an unnecessary json stringification + parsing happening.

At some point in previous versions (3.38.x at least), the first form would sometime "work" (though not always), I guess because of internal query optimisations.

With 3.39.x it never "works", which is more consistent (good), but slower with the extra json() (less good).

I do not know how complex it would be to add/preserve JSON affinity as distinct from text/strings, but now that JSON is part of SQLite core, it would be nice for JSON to have first-class support (alongside like numbers or strings).

Of course this would have uses beyond sorted arrays, another use case is when aggregating multiple datasets in a single hierarchical JSON output, where extra json() functions currently have to be thrown in as well.

Thanks

At some point in previous versions (3.38.x at least), the first form would sometime "work" (though not always), I guess because of internal query optimisations.

See b812a84493c1d129 and its responses for the explanation.

Yes, and there were similar questions back when JSON1 was introduced as an extension.

As an intermediate step, would it be feasible/useful to have

• 'json' added to the list of text-affinity (https://www.sqlite.org/datatype3.html#determination_of_column_affinity)
• intermediate subqueries returning json use it to qualify the columns as a declared type (https://www.sqlite.org/c3ref/column_decltype.html)
• have the json functions recognize this declared type when field type is text, and bypass parsing when aggregating for instance by assuming the text is well-formed json content.

Additionally, the well-formed-ness of JSON could be enforced by a constraint on those field, in cases where it's important. It can already be done with a check constraint, but having a standard JSON constraint would allow the json functions to recognize it, and the check to be bypassed when the value comes out of a json function.

FWIW I am now using a "ghetto" version of the above with group_concat in place of json_group_array.

I am not sure to see what preserving the subtype would break, as long as this subtype follows the existing rules ?

I'm referring to https://www.sqlite.org/datatype3.html#determination_of_column_affinity and https://www.sqlite.org/c3ref/column_decltype.html

For instance if you "create table test (field json_text)" the declared type will be json_text, which the affinity rules in map to TEXT.

Then if you "create table test2 as select * from test", the json_text is lost, and you see in sqlite_master "create table test2 (field TEXT)" as the affinity is TEXT.

But if it was preserved verbatim, it would still not change the storage, and would be storage & behavior-compatible with a "create table test2 (field json_text)", which you could have created by separating the create table & insert (as both declare a field with TEXT affinity).

The rule in https://www.sqlite.org/c3ref/column_decltype.html would still apply, and subtypes would only survive when not manipulated by expressions.