Search using JSON field-CodePudding

I am Using MySQL 5.7 where I have below table structure:

id(bigInt)
item_name(varchar)
attributes(JSON array)

Example Data Set

1,"PRODUCT",'[ { "1": [2,4,1]},{ "2": [5,4,6]},{ "3": [5,3,2]}]'

I want to fetch the row based on the attributes field where key value is 1 (Not to include values search needs to be done on keys only).

Tried queries:

SELECT JSON_CONTAINS('{"attributes":[ { "1": [2,4,1]},{ "2": [5,4,6]},{ "3": [5,3,2]}]}',"1") Result;

This always returns 0n as result

SELECT JSON_SEARCH('{"attributes":[ { "1": [2,4,1]},{ "2": [5,4,6]},{ "3": [5,3,2]}]}', 'one',  "1") Result;

This always return null

Any help will be appreciated

CodePudding user response：

SELECT JSON_CONTAINS(
           JSON_EXTRACT(
               '{"attributes":[ { "1": [2,4,1]},{ "2": [5,4,6]},{ "3": [5,3,2]}]}', 
               '$.attributes[*].*'), 
           '1')

PS. For to understand the query execute SELECT JSON_EXTRACT(..) only.

PPS. Pay attention - the value to be found must be JSON or the value which is implicitly convertable to JSON (i.e. string and not numeric).

https://dbfiddle.uk/?rdbms=mysql_5.7&fiddle=edc90bff8eb2528708581576805ea98d

SELECT JSON_SEARCH('{"attributes":[ { "1": [2,4,1]},{ "2": [5,4,6]},{ "3": [5,3,2]}]}', 'one', "1")

this always return null

Read the function description carefully - JSON_SEARCH searches for string values only (the value to be searched for is specially marked as search_str) whereas the value to be found is numeric in JSON value.