How to count occurrences in JSON array

Help JSON
#1

I have a table with a JSON field. The field contains one array of strings like:

["apple", "orange"]
["apple"]
["banana", "orange"]
["pineapple", "banana", "orange"]

How can I get the count of each of those value ? Like this :

orange, 3
apple, 2
banana, 2
pineapple, 1
#2

Found the solution

SELECT table_col, count(*) FROM my_table JOIN TABLE(JSON_TO_ARRAY(fruit)) GROUP BY table_col;

1 Like
#3

Welcome Bananas! :wave: Thanks so much for posting a follow-up that you found a solution. We’re so glad to hear it! Wooohooo! :partying_face:

We love it when folks share their experiences like this because of the great potential it has in helping other community members that are dealing with the same thing or something similar. You never know and the next time someone searches our forums to address an issue like this, they’ll have your post to draw from. Thanks a bunch! :pray: