键，BigQuery中的值计数 [英] Key, Value Count in BigQuery

问题描述

BigQuery中以下问题的实现:

Implementation of the following problems in BigQuery:

1. 我有以下JSON格式的字典.如何计算"id"字典中的键，值的总数?

{"fil":{"property":{"id":{id_1:"a"，id_2:"b"，id_3:"c"，id_4:"d"}}}}}

{"fil":{"property":{"id":{id_1:"a",id_2:"b",id_3:"c",id_4:"d"}}}}

1. 值"a"可以出现在多个此类词典的任何id(id_1，...，id_5)中.需要计算"a"出现在任何词典的任何id中的次数.

推荐答案

由于其他答案对我来说很难，所以我制作了适用于string:int dict的正则表达式

As other answers were to hard for me I made regular expression that works for string:int dict

SELECT 
        *, REGEXP_EXTRACT_ALL(my_dict_column, r'"(\w+": \d+)') as keys
FROM test.test_table

由此您可以执行键，值等

From that you can do keys, values and etc

这篇关于键，BigQuery中的值计数的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！