How to get the length of an array of objects in MySQL Json column where the containing objects met a condition

Question

I am trying to query a Mysql json column that contains an array of objects. I want the length of the array where a condition is met in the objects

Example of the JSON array:

[
    {"userId": 100, "type": 1, "status": 1},
    {"userId": 101, "type": 2, "status": 1},
]

Current Query:

SELECT IFNULL(JSON_LENGTH(json_users), 0) AS my_count, post_id 
FROM posts WHERE post_id = 6421028173277829027 
AND json_contains(json_users, '{"type" : 1, "status": 1}');

This will return the following whic is desired

+------------+---------------------+
| my_count   | post_id             |
+------------+---------------------+
|          1 | 6421028173277829027 |
+------------+---------------------+

But if I change the query where param 'type' to say 3

SELECT IFNULL(JSON_LENGTH(json_users), 0) AS my_count, post_id 
FROM posts WHERE post_id = 6421028173277829027 
AND json_contains(json_users, '{"type" : 3, "status": 1}');

I get an empty set. But I'm trying to get the following:

+------------+---------------------+
| my_count   | post_id             |
+------------+---------------------+
|          0 | 6421028173277829027 |
+------------+---------------------+

I assume I have to change the where clause and somehow check inside the following:

IFNULL(JSON_LENGTH(## json_users ##), 0)

but I don't know where to start

EDIT:

The following query returns an array of paths to the objects so I can use JSON_LENGTH to get the count:

SELECT JSON_LENGTH(IFNULL(JSON_SEARCH(json_users->>'$[*].type', 'all', 1), JSON_ARRAY())) AS my_count
FROM posts WHERE post_id = 6421028173277829027;

Jeremy R DeYoung · Accepted Answer · 2019-02-13 15:35:57Z

30

Did you try

SELECT JSON_LENGTH(JSON_EXTRACT(json_users, "$.type")) FROM posts

I know there is a bug where if json_users is null, it will not return anything.

answered Feb 13, 2019 at 15:35

Jeremy R DeYoung

4314 silver badges6 bronze badges

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

Comments

kristakis · Accepted Answer · 2023-08-25 00:17:14Z

2

To extend on Jeremy R DeYoung's answer, if you want to have a lack of the JSON field show as a length of 0 just use COALESCE eg:

COALESCE(JSON_LENGTH(JSON_EXTRACT(json_users, "$.type")),0)

answered Aug 25, 2023 at 0:17

kristakis

214 bronze badges

Collectives™ on Stack Overflow

How to get the length of an array of objects in MySQL Json column where the containing objects met a condition

2 Answers 2

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related