jsonの配列数をSQLで取得したい

Question

サンプルデータ

user_id	follow_user_id
1	{5, 6, 8, 10}
2	{4, 5, 7, 11}
3	{8}
4	{3, 5, 9, 12, 15, 20}
5	{}

実現したいこと

次のようなOutputデータをSQLで取得したいです

user_id	count_follow_user_id
1	4
2	4
3	1
4	6
5	0

現状について

• FirestoreのデータをRedashに連携させてます
• Redash上でSQLを書いて実行したいのですが、上手く行かず配列数を取得できません

Comments

[yambejp]

Firebaseですかね？タグ付けしたほうがよいかもしれません
Firebaseのデータ型や関数はよくわからないのですが
follow_user_idカラムのデータはJSONデータには見えないですが
Firebaseでは許されているのでしょうか？

[akio0922]

firestoreになります。そして自己解決しました！気にかけていただきありがとうございます。

Answer 1

UNNEST関数を使うことで自己解決しました。

やったこと

• From句にUNNEST関数でfollow_user_idを紐付け、配列形式を1つずつにバラす
• countを算出するためuser_idをgroup by

Answer 2

MySQL 分析関数(ウインドウ関数)をわかりやすく説明してみたを使うと便利なのにすぐ配列で設計して苦労する人の多いこと。