Query on matching json array keys

Question

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

wchiquito · Accepted Answer · 2018-12-27 22:58:33Z

1

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.

edited Dec 27, 2018 at 22:58

answered Dec 27, 2018 at 21:37

wchiquito

16.7k2 gold badges37 silver badges46 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

user10332687 Over a year ago

wow, that's pretty complex for doing a json query. Thanks for sharing. Unfortunately, I'm using mysql5.7, which doesn't support some of the above, but I tested it in the db-fiddle and it's pretty neat.

Thomas Skubicki · Accepted Answer · 2018-12-27 20:33:28Z

0

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

answered Dec 27, 2018 at 20:33

Thomas Skubicki

6675 silver badges12 bronze badges

Collectives™ on Stack Overflow

Query on matching json array keys

2 Answers 2

1 Comment

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

1 Comment

Comments

Your Answer

Sign up or log in

Post as a guest

Related