mysql extract_json value from array

Question

I have a MySQL DB containing a table named "products". This table contains a Json Data_type column named "values".

I would like to find the path to extract a specific value :

select JSON_EXTRACT(values, '$.COD') from products where id = '1'

returns :

"COD": {"<channels>": {"<locales>": "3699999999999"}}

And what I want is "3699999999999".

It is pretty obvious that my path is not the good one, but I can't find the solution.

Thanks for your help !

Hello and welcome. As newcomer reading of How to Ask and minimal reproducible example is good practice to learn how to ask a good question. For this case, you should provide the table schema. — Baptiste Mille-Mathias
– Baptiste Mille-Mathias, Commented Aug 31, 2018 at 10:04
Thank you, I tried, but I get an error regarding the '<' and '>' chars : "Invalid JSON path expression. The error is around character position 26." — Boons
– Boons, Commented Aug 31, 2018 at 12:30
You rock wchiquito ! How did I miss it ? Anyway, is there a way to select your answer as solution ? — Boons
– Boons, Commented Sep 3, 2018 at 9:04

Sonam Tripathi · Accepted Answer · 2018-08-31 12:49:41Z

1

You can try

SELECT JSON_EXTRACT(`values`, '$.COD.<channels>.<locales>') FROM products WHERE id = '1';

For more details please refer mysql-for-your-json

edited Aug 31, 2018 at 12:49

answered Aug 31, 2018 at 11:02

Sonam Tripathi

1831 silver badge12 bronze badges

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

3 Comments

Boons Over a year ago

Thanks, I tried it but I get an error regarding the special chars '<' and '>'. "Invalid JSON path expression. The error is around character position 26."

Sonam Tripathi Over a year ago

Could you please try with backticks as <channels>.<locales>

Boons Over a year ago

Thanks again, I tried ---> select JSON_EXTRACT(values, '$.COD.<channels>.<locales>') and ----> JSON_EXTRACT(values, '$.COD.<channels>.<locales>') but same error twice.

Collectives™ on Stack Overflow

mysql extract_json value from array

1 Answer 1

3 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

3 Comments

Your Answer

Sign up or log in

Post as a guest

Related