CODEWITHSUNDEEP
mysqlmysql-5.7
Hemant singh
Hemant singh

Reputation: 35

Search using JSON field

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

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

Upvotes: 0

Views: 343

Answers (1)

Akina
Akina

Reputation: 42764

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.

Upvotes: 1

Related Questions