[META] Add support for JSON field queries in entity queries

To summarize and better organize that discussion:

So my idea was to create field tables with identifiers and a JSON text field so like nid,vid,langcode,uuid,field_data. Then store $.fieldname[delta].property in field_data. The $.fieldname[delta].property expression is JSONPath notation which is what the databases use too. Entity query backends would utilize the backend overrideable mechanism, there's already a pgsql.entity.query.sql, mysql.entity.query.sql and sqlite.entity.query.sql services could be added. The queries themselves would use whatever JSON deconstructing functionality is made available by the database, I only tested MySQL and SQLite but I trust PostgreSQL to have similar.

mysql> insert into node_new values(12, '{"nid":[{"value":12}],"title":[{"value":"node title"}],"body":[{"value":"node body"}],"multi":[{"value":"a"},{"value":"b"}]}');
Query OK, 1 row affected (0.03 sec)
mysql> SELECT nid FROM node_new, JSON_TABLE(field_data,'$.multi[*]' COLUMNS (value VARCHAR(4096) path '$.value')) test WHERE test.value='b';
+-----+
| nid |
+-----+
|  12 |
+-----+
1 row in set (0.00 sec)

This requires MySQL 8.0 / MariaDB 10.6.0 and while there are tricks for earlier versions to do the querying, I believe indexing is not possible based on https://stackoverflow.com/q/35198140/308851 and that's probably enough to not want to do it. But just for completeness sake, one could utilize https://stackoverflow.com/a/51656467/308851 create the row table and write this most beautiful of queries:

SELECT nid, JSON_EXTRACT(field_data,CONCAT('$.multi[',B._row,']')) x FROM node_new INNER JOIN t_list_row AS B ON B._row < JSON_LENGTH(field_data->'$.multi') HAVING x->>'$.value'='b';

Again, it's possible I am wrong but StackOverflow is usually reliable enough, even in negatives. As a side note, even bona fide document databases used to struggle with indexing arrays https://jira.mongodb.org/browse/SERVER-826

SQLite, same table:

sqlite> SELECT nid FROM node_new,JSON_EACH(field_data,'$.multi') test WHERE JSON_EXTRACT(test.value,'$.value') = 'b';
12

Based on https://news.ycombinator.com/item?id=13556517 these were present in JSON1. Indexing is left as an exercise to the reader :) but https://dgl.cx/2020/06/sqlite-json-support looks very relevant. 3.31.0 might be needed.

Overall, due to the version requirements and the amount of work required, I suspect this might be a Drupal 11 feature.

Finally, a note on languages: yes, this would require storing non-translatable values multiple times, once for each translation but I believe moving the language into the field data would get even messier. But these are just my starting thoughts and if this is indeed Drupal 11 then there's plenty of time for research and experimentation. I do not intend to take this issue any further than this.

I started exploring this issue in my case to provide some ability to query data which is otherwise stored in a map data type...which right now is just serialized. I haven't seen map used in the wild a ton, but e.g. Commerce has a data field on some entity types which could be a real gold mine for this kind of querying.

Adding a JSON alternative to map vs. converting the entity storage itself to JSON are two very different things. I think it's best handled in two almost completely independent issues. That's definitely a smaller change that could probably be done much easier.

For entity storage, this is very interesting, however the massive barriers will be the upgrade path and backwards compatibility for things like views data. It might need to be a new module, that starts out experimental, and might even need to stay opt-in after becoming stable so that sites can migrate on their own schedule. Then if it's successful we could eventually move the old entity sql storage classes to contrib and deprecate in core or something like that.

It missing issue for sqlite and display at status page

I'm all with catch (#5) in that this needs workable steps. Started to code a json_map_field (will share soon, collaborators welcome), but realized that this is currently impossible, as serialization of the builtin map field relies on a megadeth kittenkiller hack.

Started some work in the related (9y old!) issue.

The title of this issue says "entity queries", but to support this in entity queries we must first support this in Drupal's database API itself. I opened #3378275: Add support for JSON field queries in database API as a child issue of this meta for discussing and implementing that step.