How to use JSON_EXTRACT to get values of a string index

Question

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?

gen_Eric · Accepted Answer · 2020-11-04 18:28:48Z

2

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").

edited Nov 4, 2020 at 18:28

answered Nov 4, 2020 at 18:12

gen_Eric

228k42 gold badges304 silver badges343 bronze badges

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

2 Comments

Maykel Esser Over a year ago

Nice! Almost there! Now, can i get the key values on a alias field, like my example?

gen_Eric Over a year ago

@MaykelEsser Check the updated answer, you can just use JSON_EXTRACT() with the same path.

Collectives™ on Stack Overflow

How to use JSON_EXTRACT to get values of a string index

1 Answer 1

2 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

2 Comments

Your Answer

Sign up or log in

Post as a guest

Related