MySQL 8 search JSON key by value in array

Question

I've got MySQL table with JSON field, where I store data in such a format.

{
  "fields": {
    "1": {
      "s": "y"
    },
    "2": {
      "s": "n"
    }
  }
}

I need to obtain the keys in fields, e.g. 1 or 2 given the value of s.

Example query:

create table mytable ( mycol json );

insert into mytable set mycol = '{"fields": {"1": {"s": "y"},"2": {"s": "n"}}}';


select j.* from mytable, JSON_TABLE(mycol,
'$.fields.*' COLUMNS (
    json_key VARCHAR(10) PATH '$',
    s VARCHAR(10) PATH '$.s'
  )
) AS j where j.s = 'y';

gives:

# json_key, s
null, y

I would expect to get

# json_key, s
1, y

Is it possible to get that data somehow?

I don't need the results in row / table format. I would be happy to get the comma separated list of IDs (json_keys) meeting my criterium.

EDIT: I was also thinking about getting the paths using JSON_SEARCH and passing that to JSON_EXTRACT, this was achieved here: Combining JSON_SEARCH and JSON_EXTRACT get me: "Invalid JSON path expression." Unfortunately the difference is that I would need to use JSON_SEARCH in all mode, as I need all results. In such a mode JSON_SEARCH returns list of paths, where as JSON_EXTRACT accepts list of arguments.

score 1 · Accepted Answer · answered Feb 18 '20 at 05:06

Try FOR ORDINALITY (see 12.17.6 JSON Table Functions), this type enumerates rows in the COLUMNS clause:

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'
  )
) `j`
WHERE
  `j`.`s` = 'y';

See dbfiddle.

MySQL 8 search JSON key by value in array

1 Answers1