CODEWITHSUNDEEP
pythonmysqlsql
user10332687
user10332687

Reputation:

Query on matching json array keys

I have a json field in mysql called column_info. Here's an example value:

[{"id": ["132605", "132750", "132772", "132773", "133065", "133150", "133185", "133188", "133271", "133298"]}, 
 {"number": ["1", "2", "3", "4", "5", "6", "7", "8", "9", "10"]}, 
 {"id": ["1", "1", "1", "1", "1", "1", "1", "1", "1", "1"]}
]

Is it possible to do a query that does the following:

keys = ['id', 'number', 'id']
SELECT * FROM `column_info` WHERE JSON(??) = keys

Upvotes: 0

Views: 67

Answers (2)

wchiquito
wchiquito

Reputation: 16551

Try:

SELECT `column_info`
FROM
  `table`,
  (SELECT @`keys` := '["id", "number", "id"]') `init`
WHERE (
  SELECT
    JSON_ARRAYAGG(
      JSON_UNQUOTE(
        JSON_EXTRACT(
          JSON_KEYS(`der`.`keys`),
          '$[0]'
        )
      )  
    )
  FROM
    JSON_TABLE(
      `column_info`,
      '$[*]'
      COLUMNS(
        `keys` JSON PATH '$'
      )  
    ) `der`
) = CAST(@`keys` AS JSON);

See db-fiddle.

Upvotes: 1

Thomas Skubicki
Thomas Skubicki

Reputation: 637

What version of MySQL are your running?

If it's v8, you can try JSON_CONTAINS()

https://dev.mysql.com/doc/refman/8.0/en/json-search-functions.html

Upvotes: 0

Related Questions