MYSQL JSON - extracting part of JSON field using WHERE on its sibling key value

Question

I'm trying to extract subset of JSON document value based on adjacent key value.

My JSON string:

[
  {
    "_metadata": {
      "id": 1
    },
    "_children": [
      "A",
      "B",
      "C"
    ]
  },
  {
    "_metadata": {
      "id": 2
    },
    "_children": [
      "X",
      "Y",
      "Z"
    ]
  }
]

Is it possible to return just [X,Y,Z] when setting WHERE clause like $._metadata.id="2" ?

Thank you!

Answer 1

One option is:

SELECT
  `der`.`_children`
FROM
  JSON_TABLE(
    @`json`,
    '$[*]'
    COLUMNS(
      `id` INT PATH '$._metadata.id',
      `_children` JSON PATH '$._children'
    )
  ) `der`
WHERE
  `der`.`id` = 2;

See dbfiddle.