MySQL 8.0

By using db<>fiddle, you agree to license everything you submit by Creative Commons CC0.

SELECT VERSION();


VERSION()


8.0.27


DROP TABLE IF EXISTS `mytable`;

CREATE TABLE IF NOT EXISTS `mytable` (
  `mycol` JSON
);

INSERT INTO `mytable`
SET `mycol` = '{"fields": {"1": {"s": "y"}, "2": {"s": "n"}}}';
SELECT
  JSON_UNQUOTE(
    JSON_EXTRACT(
      JSON_KEYS(`mycol` ->> '$.fields'),
      CONCAT('$[', `j`.`row` - 1, ']')
    )
  ) `json_key`,
  `j`.`s`
FROM
  `mytable`,
  JSON_TABLE(
    `mycol`,
    '$.fields.*' COLUMNS (
      `row` FOR ORDINALITY,
      `s` VARCHAR(10) PATH '$.s'
  )
) AS `j`
WHERE
  `j`.`s` = 'y';


json_key
s


1
y