MySQL Search within a JSON Array from a Select Statement

Question

I am new to JSON in MySQL. I am using MySQL 5.7.

I am attempting to search within a JSON Array for values from another field.

Example.

CREATE TABLE t1 (jID INT UNSIGNED NOT NULL AUTO_INCREMENT, jdoc JSON, PRIMARY KEY(`Jid`));
CREATE TABLE t2 (ID INT);

INSERT INTO t2 VALUES (1);
INSERT INTO t2 VALUES (2);
INSERT INTO t2 VALUES (3);

INSERT INTO t1 (JDOC) VALUES
          ('{"a": 17, "b": "red"}'),
          ('{"a": 17, "b": "red", "x": [3, 5, 7]}');
          
SELECT t1.*, JSON_EXTRACT (jdoc, '$.x') AS A, JSON_CONTAINS(JSON_EXTRACT(jdoc, '$.x'),'3','$') AS B FROM t1;

Works as expected.

| jID | jdoc | A | B | |------|----------------|---------|---------| | 1 | {"x": "red"} | "red" | 0 | | 2 |{"x": [3, 5, 7]}|[3, 5, 7]| 1 | What I want to run is

SELECT t1.*, JSON_EXTRACT (jdoc, '$.x') AS A, 
JSON_CONTAINS(JSON_EXTRACT(jdoc, '$.x'), (SELECT ID FROM t2),'$') AS B FROM t1;

However I can't work out how to make this work and get an Invalid Data type as it is expecting a string or int, not "(Select ID from t2)".

However this is just an intermediate step. What I really want to write is some code that finds all the values in t2.ID that do not exist in x in a passed in JSON Array ('{"a": 17, "b": "red", "x": [3, 5, 7]}');

To me this seems a fairly standard request. I have a Stored Procedure being passed a lot of information, and I need to see if the information already exists in the database, to know what to do with it. I achieve this by comparing ID values of x against the ID values in a table.

Edit. The more I think about it, what I'm wanting is the opposite of the JSON_Array call. Something that converts an array back to a list.

Edit2. I see you could achieve it with JSON_TABLE in version 8. Anything in versino 5.7?

"*Works as expected. *" - the shared output doesn't correspond to your code's output >> https://www.db-fiddle.com/f/sUySTeW7YPakKroazY195H/0. Are you sure your tables definition is correct? — lemon, Jul 02 '22 at 12:50
Please clarify your specific problem or provide additional details to highlight exactly what you need. As it's currently written, it's hard to tell exactly what you're asking. — Community, Jul 02 '22 at 19:15
I have re-written the example to be clearer. I have also expanded on what I'm trying to achieve at the end. — Derek McKinnon, Jul 02 '22 at 21:12

score 0 · Answer 1 · answered Jul 03 '22 at 21:55

I have written a stored procedure which converts the array to a list.

CREATE FUNCTION `Convert_JSON_Array2List`(JArray JSON)
RETURNS VARCHAR(2000)
DETERMINISTIC

Convert_JSON_Array2List:BEGIN
DECLARE opStringList VARCHAR(2000) DEFAULT NULL;

    SET opStringList = NULL;
    
  -- Check valid JSON

  IF ISNULL(JArray) THEN    

    RETURN opStringList;
    LEAVE Convert_JSON_Array2List;

  END IF;

  IF NOT JSON_VALID(JArray) THEN
    
    RETURN opStringList;
    LEAVE Convert_JSON_Array2List;

  END IF;
  
  -- TODO.  Write more checks.
  -- including that this is just a JSON array and not a JSON Object.
  
  -- Calculate Result
SET opStringList := 
  REPLACE(
   REPLACE(
      REPLACE(
       JArray
       , '['
       , ''
     )
     , ']'
     , ''
   )
   , '"'
   , ''
 );

RETURN opStringList;

END$$

MySQL Search within a JSON Array from a Select Statement

1 Answers1