MySQL Bugs: #98726: Sub Query Json Extraction Not Considered Result Set of Array Values?

Bug #98726	Sub Query Json Extraction Not Considered Result Set of Array Values?
Submitted:	24 Feb 2020 17:42	Modified:	10 Mar 2020 18:17
Reporter:	Jeremy Walker	Email Updates:
Status:	Not a Bug	Impact on me:	None
Category:	MySQL Server: JSON	Severity:	S3 (Non-critical)
Version:	8.0.18 Community Edition	OS:	Ubuntu
Assigned to:		CPU Architecture:	x86
Tags:	IN, json_extract

Description:
using json_extract to get array of values cannot be used as a collection to be used in an "IN" statement.

How to repeat:
Table 1:
items | id (INT) | name (VARCHAR 80)

Table 2:
recipes | id (INT) | name (VARCHAR 80) | ingredients (JSON)

Json stored in ingredients would be something like:
[
 {
  {"id":15}, {"qty":1}
 },
 {
  {"id":4}, {"qty":1}
 }
]

the ID in the json id field is the id of the row in Table 1. So when I extract it with a single query:

SELECT JSON_EXTRACT( ingredients, '$[*].id') FROM recipes WHERE id = 1;

It works fine, returning the value: [15,4]

The problem lies in trying to use that as a source of 'results' for an IN statement.

SELECT *
FROM items AS item
WHERE item.id IN (
    SELECT JSON_EXTRACT(ingredients,'$[*].id')
    FROM recipes
    WHERE id = 1
) 

will return 0 results.

Perhaps it does not know the difference between [15,4] and (15,4)?

Suggested fix:
Either JSON_EXTRACT_COLLECTION() so it would return (15,4) instead of the json object, providing that all the values being returned are the same types.

Allow IN to iterate through an array of the same types of values as it does with the collection set currently.

Hi Mr. Walker,

Thank you for your bug report.

However, we do not think that it is a bug. Let us analyse it for you. You have a query:

SELECT * FROM items AS item WHERE item.id IN (   SELECT JSON_EXTRACT(ingredients,'$[*].id')    FROM recipes   WHERE id = 1 ;

where the nested query returns : [15.4].

That means that the above query is transformed into:

 SELECT * FROM items AS item WHERE item.id IN ( [15.4]);

Of course that this query can not return any results.

Our Reference Manual contains some examples of different usage of JSON_EXTRACT() in the paragraph 12.17.3.

Not a bug.

Yes I read the manual, where it states:

The return value consists of all values matched by the path arguments. If it is possible that those arguments could return multiple values, the matched values are autowrapped as an array, in the order corresponding to the paths that produced them. Otherwise, the return value is the single matched value.

But not being able to handle an array return result in an IN statement, kind of adds a limit to the functionality of the column type wouldn't you say?

You are pretty much limited to using json_extract for things like comparators in a WHERE ( WHERE JSON_EXTRACT(c, "$.id") > 1 ) and actually SELECT the values using it ( JSON_EXTRACT(c, "$.id") ).

If I had the ability to use the Array value of json data in a column, it would remove the need for a second table of values (Which I have built from the JSON data), to do a second table just to store the counts of ingredients by recipe.

CREATE TABLE recipeingredients (
  `id` INT NOT NULL AUTO_INCREMENT,
  `recipeid` INT NULL,
  `ingredientid` INT NULL,
  `quantity` INT NULL,
  PRIMARY KEY (`id`)
);

Then I did basically my query that I was doing before to extract the json, and then parse the array in a php script, to insert that data into the table.

So what I am now left with is a query like this:

with
	rQ1 as (
		select id as rid, 
		name as recipename, 
		type as recipetype, 
		ingredients, 
		mealeffect, 
		statuseffect from recipes where id = 1
		),
    iQ1 as (
		select id as itemid, 
		name as ingredientname, 
		recipeid as itemrecipeid, 
		vendorbuy, 
		vendorsell from items 
		where id in (
			select ingredientid from recipeingredients 
			where recipeingredients.recipeid = 1)
		)
select * from rQ1 join iQ1

So I have this massive query, just to do something that should be usable in the original query in some fashion, just by pulling the values out of the JSON_EXTRACT within the query using the IN statement.

Hi,

As I have explained in my previous comments, there are other methods that you can try with this function.

If they do not work, then you could write a stored routine that would create a SELECT statement from JSON_EXTRACT() output.

Last, but not least, you can look at the JSON_CONTAINS() functionality.