Support JSON fields in Sqlite schema

Here's initial patch with tests:
- all results are returned as strings
- Difference in using -> vs ->> operators (from docs)

The right-hand operand to the -> and ->> operators can be a well-formed JSON path expression. This is the form used by MySQL. For compatibility with PostgreSQL, the -> and ->> operators also accept a text label or integer as their right-hand operand. If the right operand is a text label X, then it is interpreted as the JSON path '$.X'. If the right operand is an integer value N, then it is interpreted as the JSON path '$[N]'.

fix CS

fix CS again

Modernized tests as the current CI images has sqlite version 3.34

+++ b/core/modules/sqlite/tests/src/Kernel/sqlite/SchemaTest.php
@@ -100,4 +100,70 @@ public function testIntrospectIndexSchema(): void {
+    $query->addExpression('test_field -> :key', NULL, [

to support this syntax Sqlite should be version >= 3.38.0 https://www.sqlite.org/json1.html#jptr

+++ b/core/modules/sqlite/tests/src/Kernel/sqlite/SchemaTest.php
@@ -116,54 +118,36 @@ public function testJsonSchema(): void {
+    // Before 3.38.0 no operators.
...
+      if (!$hasOperator) {

fix typo incondition

Super awesome work @andypost thank you very very much for pushing things forward!

Code looks good, tests are green, so I think we can set this RTBC!

+++ b/core/modules/sqlite/tests/src/Kernel/sqlite/SchemaTest.php
@@ -100,4 +100,54 @@ public function testIntrospectIndexSchema(): void {
+    // Before 3.38.0 no operators.
+    $hasOperator = (version_compare($version, '3.38', '>='));

This is a bit of an ugly solution. Maybe better to wait for D11. Where the minimum required version of SQLite will > 3.38.

This is a bit of an ugly solution. Maybe better to wait for D11. Where the minimum required version of SQLite will > 3.38.

Good point! Could you add that as suggestion to the META issue, so it might be better so not commit that to D10 in general?

For what it's worth, I think this approach would be fine, so long as a follow-on issue was added to cleanup the hackery and bump the requirements in D11.

Filed MR to upgrade PHP 8.3 image to use latest Debian which is shipped with Sqlite 3.40.1

https://git.drupalcode.org/project/drupalci_environments/-/merge_request...

Per #3343634-14: Add "json" as core data type for Schema API → this is being consolidated into the parent issue for a single change.