Conditionally select values from an array in a nested JSON string in a Mysql database

Question

I am struggling to conditionally extract values from a nested JSON string in the Mysql table.

{"users": [{"userId": "10000001", "userToken": "11000000000001", "userTokenValidity": 1}, {"userId": "10000002", "userToken": "12000000000001", "userTokenValidity": 1}, {"userId": "10000003", "userToken": "13000000000001", "userTokenValidity": 0}]}

I want to select a userToken but only if the userTokenValidity is 1. So in this example only "11000000000001" and "12000000000001" should get selected.

This will extract the whole array ... how should I filter the result?

SELECT t.my_column->>"$.users" FROM my_table t;

You need to use JSON_TABLE(), which means you need to use MySQL 8.0. What does SELECT VERSION(); return? — Bill Karwin
– Bill Karwin, Commented May 13, 2022 at 14:23

Akina · Accepted Answer · 2022-05-13 15:01:19Z

2

SELECT CAST(value AS CHAR) output
FROM test
CROSS JOIN JSON_TABLE(test.data, '$.users[*]' COLUMNS (value JSON PATH '$')) jsontable
WHERE value->>'$.userTokenValidity' = 1

https://dbfiddle.uk/?rdbms=mysql_8.0&fiddle=4876ec22a9df4f6d2e75a476a02a2615

answered May 13, 2022 at 15:01

Akina

43.2k6 gold badges17 silver badges30 bronze badges

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

Collectives™ on Stack Overflow

Conditionally select values from an array in a nested JSON string in a Mysql database

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related