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 Active 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 Active 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 📌 Prevent modules from being uninstalled if they provide field types used in an Experience Builder field Fixed 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.

I think needs to be stable blocker or we need to figure out a different solution that would allow FieldTypeUninstallValidator to work with all supported databases. Otherwise I think nothing would prevent you from uninstalling this module while the field type is still in use

+1 for the reply from @bradjones1 in comment #18.

Let do ✨ Add "json" as core data type Active first and build the solution for this issue on top of JSON in DBAL.

Even if JSON support can be added for this case, I'm not convinced that's the right solution overall, because there are other problems with the nested JSON structure which issues like 📌 Refactor the XB field type to be multi-valued, to de-jsonify the tree, and to reference the field_union type of the prop values Active are discussing and which might mean it's not necessary to do this at all. For me I would postpone this on 📌 Refactor the XB field type to be multi-valued, to de-jsonify the tree, and to reference the field_union type of the prop values Active and only worry about it if that issue (or something similar) doesn't happen.

I agree with @catch in #23.