Ensure querying JSON nested values when parent keys are unknown is possible in all supported databases

@tedbow Have you checked the prior art in ✨ Add "json" as core data type to Schema and Database API Needs work and 🌱 [Meta, Plan] Pitch-Burgh: JSON field storage & JSON field schema support Active yet?

@Wim Leers I have started to look at ✨ Add "json" as core data type to Schema and Database API Needs work and made a couple comments as I start to understand it. It doesn't seem to solve this problem yet.

I asked in @bradjones1 about this in Drupal slack to see if he has any ideas or run into this problem.

@tedbow Does that mean this issue should be marked postponed on that core issue?

@Wim Leers I would prefer not to postpone this issue. I think we should still explore if the DB's support what we need. Regardless of what nice API core puts around the DB differences we will need the DB's to support the basic functionality or be ok with the work arounds we would have to implement.

I think it would be also good to start to list the ways we think we need will need to search the json field and whether those needs will involve this unknown parent keys problems.

An example

1. Entity queries. An an example $query->jsonPropertyCondition($xb_field, 'props', $json_path, '%some string%', 'LIKE')

   Outside of the use case of 📌 [PP-1] Prevent modules from being uninstalled if they provide field types used in an Experience Builder field Postponed and other logic we might need in uninstalls, where maybe we could make utility functions, we would need to search without knowing all the parent keys?

   From our previous test data
   'props' => '{"dynamic-static-card2df":{"text":{"sourceType":"dynamic","expression":"\u2139\ufe0e\u241centity:node:article\u241dtitle\u241e\u241fvalue"},"href":{"sourceType":"static:field_item:link","value":{"uri":"https:\/\/drupal.org","title":null,"options":[]},"expression":"ℹ︎link␟uri"}}}',

   If we wanted to be able to do this we would to have unknown parent keys, because if we wanted to search for any link field in any component that linked to the domain drupal.org we would never know the component IDs so that would be at least be one parent key we would not know.

But do we for need this kind of ability?

I am going to try experiment to get the \Drupal\experience_builder\FieldTypeUninstallValidator::checkContentEntityUses to work with all DB's by making a utility class

AFAICT this is a WIP, reflecting in issue state.

Unassigning for now. Feel free to pick this up in case anyone is interested in helping with adding support for PostgreSQL and SQLite.

I hadn't seen this issue, but I opened this one about denormalizing instead a few weeks ago too 📌 Calculate field and component dependencies on save and store them in an easy to retrieve format Active .

Thanks, @Bvuaneshwar, looking forward to what you do here! 🤓😊

Asked @catch to see if he agrees #3457504 could be an escape hatch if this turns out to be impossible: #3457504-5: Calculate field and component dependencies on save and store them in an easy to retrieve format → .

@tedbow, I looked for some workaround for issue #8 with the prefixTables function. Can we override Drupal's prefixTables method to handle JSON paths correctly?

public function prefixTables($sql) {
// Custom method to handle JSON paths
$sql = $this->handleJsonPaths($sql);

// Let Drupal handle its own prefixing
return \Drupal::database()->prefixTables($sql);
}

private function handleJsonPaths($sql) {
// Protect JSON paths from being altered by prefixTables
return preg_replace_callback('/->\'{[^}]+}\'/', function($matches) {
return str_replace(['{', '}'], ['__JSON_START__', '__JSON_END__'], $matches[0]);
}, $sql);
}

However, I'm not sure where to test or run this. Can you help me with how to reproduce this?

Per #14 I answered in the other issue but didn't see it had been referenced again here.

I personally think we should just do 📌 Calculate field and component dependencies on save and store them in an easy to retrieve format Active and not attempt this issue at all.

#15 shows exactly the sort of complexity the current approach is introducing and it should be a sign that it needs to be stepped back from. Maybe it will be possible to find another approach that doesn't involve experience builder attempting to override low-level parts of the database API, even if it is, that's a lot of work that could just be avoided.

Ted name-checked me in #4 and we did talk about this in some depth in Slack, though that conversation didn't get reflected here.

I am not working on XB but appreciate the intersection of JSON data and XB, and the kinds of problems you're trying to solve.

The issue, really, is SQLite. As it has been all along with JSON data storage in core. SQLite is just not on par with the other supported drivers.

Querying for an expression within a JSON document is possible in MySQL/MariaDB and Postgres with JSON_CONTAINS() in one form or another. There is no equivalent for SQLite.

In Slack, I suggested:

if you must support SQLite, extract the part of the document that contains the thing you need to filter and implement the rest of the JSON_CONTAINS type operation in PHP.

And for supported drivers, we can implement a jsonContains() method on the new JsonConditionInterface.

While I haven't reviewed all this issue's history, I can say confidently that any code that builds a JSON condition expression without using the query builder is a fool's errand. There are too many edge cases and this is a great illustration of why we have a DBAL in the first place.

I honestly don't think you're too far from being able to do something like this in XB, though it would require a special-case for SQLite as described. I don't necessarily think that's the end of the world, but not for me to decide.

@bradjones1 thanks for the update and info

Relating a couple issue. Especially 📌 [PP-1] Consider not storing the ComponentTreeStructure data type as a JSON blob Active . I don't think that issue covers the props so this issue might still be needed.