I have the followed data rows:
I'm trying to use JSON_EXTRACT to get rows only if inside jot_locale_vars has index equals "2".
SELECT
jot.*,
(JSON_EXTRACT(`jot_locale_vars`, '$[2]')) as localeVar
FROM job_type jot
WHERE jot_excluded = ''
HAVING localeVar IS NOT NULL
But, as you can see, i've been used $[2]
, but the array indexes start to zero. So... String 1 equals to [0]
, 2 equals [1]
... and i cant use in this way.
How can i extract values if has condition by a string index?
If you are looking to see if a key exists inside of an object, then you'll want to use JSON_CONTAINS_PATH
.
SELECT `jot`.*,
`jot_locale_vars`->'$[*]."2"' AS `localeVar`
FROM `job_type` AS `jot`
WHERE `jot_excluded` = 0
AND JSON_CONTAINS_PATH(`jot_locale_vars`, 'one', '$[*]."2"')
Note: This requires MySQL 5.7+
Note 2: The ->
operator is just shorthand for JSON_EXTRACT()
.
The syntax for the path can be found at: https://dev.mysql.com/doc/refman/5.7/en/json.html#json-path-syntax
I'm using $[*]."2"
, which means "any array value" ([*]
) that contains a key named "2"
(."2"
).