[MDEV-26182] Create a function to check for JSON intersection Created: 2021-07-20  Updated: 2023-08-25  Resolved: 2023-07-24

Status: Closed
Project: MariaDB Server
Component/s: JSON
Fix Version/s: 11.2.1

Type: Task Priority: Minor
Reporter: Abdullah Assignee: Rucha Deodhar
Resolution: Fixed Votes: 0
Labels: JSON, Preview_11.2, beginner-friendly, gsoc22, intersection

Attachments: PNG File 2022-04-03.png    
Issue Links:
Blocks
Relates
relates to MDEV-31411 JSON_ARRAY_INTERSECT/JSON_OBJECT_FILT... Closed
relates to MDEV-31543 ASAN heap-buffer-overflow in strncpy ... Stalled

 Description   

Creating a function that checks for intersection between objects would be really helpful.

For example, if we have the following 2 objects:

{'a': '1', 'b': '1', 'c': '1', 'd': '1'} {'b': '1', 'd': '1'}

And we would like to find/check for the intersection between them, we do not have a way to achieve that.

This post (https://stackoverflow.com/questions/44687872/find-intersection-of-mysql-json-objects-or-arrays) on Stackoverflow.com can possibly explain what I mean.

Final Implementation:

The idea was to have simple functions that the user can combine to produce the exact result one wants. Having two object how do we know what the user desires - to get the intersection for same keys, or same key/value pairs.
If objects have same key with an array as a value - does the user need the value of the first object, or the second or the intersection of these arrays? WE can't know for sure, and having small exact functions allows user to combine them to get what they need.

Hence tweaked the implementation made by gsoc student a little and also added more json functions. So Now we have:

  • JSON_ARRAY_INTERSECT() : Find intersection between two jsons of array type,
  • JSON_OBJECT_FILTER_KEYS(): This will take two args. first will be an object and second will be array. It will output those key-value pairs from json object that are there in the array.
  • JSON_OBJECT_TO_ARRAY(Obj) : Converts key-value pairs in the outer most level to array.

Idea: Alexey Botchkov
Implementation: Ruixiang Tan and Rucha Deodhar

 Comments   
Comment by Labnan Khalid Masum [ 2022-03-30 ]

Hello! I would like to work on this issue. I am new on MariaDB dev this. Can you help me if necessary?
Thank you

Comment by Rucha Deodhar [ 2022-03-30 ]

Hi Labnan Khalid Masum, sure. What help do you need ?

Comment by Adarsh [ 2022-03-31 ]

Hi,
Myself Adarsh from IIT Roorkee(2nd year UG)
I want to work on this issue
Please direct me how to start working on it.

Comment by Rucha Deodhar [ 2022-04-01 ]

Hi Adarsh1704 , try putting a break point in mysql_select() and execute a simple select statement with a json function similar to intersection ( JSON_CONTAINS() or JSON_OVERLAPS() for instance) and observe how it is executed and result is returned. Or maybe put break point in func_json_contains::val_int() or func_json_overlaps::val_int().

Comment by Adarsh [ 2022-04-03 ]

Hello Rucha, I am following the steps from
https://mariadb.com/kb/en/Building_MariaDB_on_Windows/
but 1 unit test is failing
and hence I am unable to start debugging process
Please suggest me the next step.

Comment by Rucha Deodhar [ 2022-04-03 ]

Adarsh1704 Don't worry about that test if you haven't modified the code yet. Sometimes tests fail because of other reasons too. Also, lets continue this on Zulip.

Comment by RinChanNOW [ 2022-04-16 ]

Hello, is this issue still in progress?

Comment by Rucha Deodhar [ 2022-04-16 ]

Hi rinchannow , this issue is open, no work has started on it yet.

Comment by Rucha Deodhar [ 2022-09-13 ]

Patch ( made by gsoc student ) : https://github.com/MariaDB/server/pull/2261/commits/dee4c38f5df43a2497cbd92dd04af5ad2e49656a

Comment by Alexey Botchkov [ 2022-09-17 ]

doubt we need that function.

Comment by Alexey Botchkov [ 2022-10-28 ]

ralf.gebhardt - i tried to explain myself there in the comment slightly above
My comment

Comment by Sergei Golubchik [ 2022-11-06 ]

What is the use case for it? Why one would need this?

Comment by Rucha Deodhar [ 2022-11-08 ]

serg maybe to filter out data without having to convert it into a table first? One json can be used as the filter and other is document to filter, if the intersection result is not null then some or all filters could be applied depending on the result.

Comment by Rucha Deodhar [ 2023-05-18 ]

Patch: https://github.com/MariaDB/server/tree/bb-11.2-MDEV-26182-json_intersect

Comment by Alexey Botchkov [ 2023-05-26 ]

see comments at the patch.

Comment by Ramesh Sivaraman [ 2023-07-19 ]

okay to push

Comment by Rucha Deodhar [ 2023-07-25 ]

( last comment that Alexey is referring to is fixed, btw)

Generated at Thu Feb 08 09:43:22 UTC 2024 using Jira 8.20.16#820016-sha1:9d11dbea5f4be3d4cc21f03a88dd11d8c8687422.