SQL-从数组中提取JSON [英] SQL - Extract JSON from Array

问题描述

我正在用Google BigQuery编写一些查询。我想从数组中提取JSON。我以某种方式可以提取它。 @Mikhail Berlyant在

我曾尝试过：

 与
 cte AS（
选择
标签，
花费
从
 BILLING.gcp_billing_export_v1）
选择
 la，
花费
从
 cte，
 UNNEST（标签）为la 
  

参见成本框，COST值重复两次，因为数组中有2个KEY，VALUE对。

因此，通过 la.key与组进行 sum（cost） code>，然后我得到了错误的值。

我正在寻找的是

有人可以帮我吗？

解决方案

下面是BigQuery标准SQL

  #standardSQL 
选择
描述，
 ARRAY（
 AS STRUCT选择
 JSON_EXTRACT_SCALAR（kv，'$ .key'）键，
 JSON_EXTRACT_SCALAR（kv，'$ .value'）值
 FROM UNNEST（SPLIT（labels，'}，{'））kv_temp，
 UNNEST（[CONCAT（'{'，REGEXP_REPLACE（kv_temp，r '^ \ [{|}] $'，''），'}'）]）kv 
）标签，
的费用为
，来自`project.dataset.table` 
  

您可以使用以下问题的伪数据摘录进行测试，玩耍

  #standardSQL 
与`project.dataset.table` AS（
 SELECT'aaa'description，'[{ key：  application， value： scaled-server}，{ key：部门， value： hrd}]'标签，成本0.323316 UNION ALL 
 SELECT'bbb'描述， '[{{key： application2， value： scaled-server2}，{ key： department2， value： hrd2}]''标签，成本为0.342825 
）
选择
描述，
 ARRAY（
 AS STRUCT选择
 JSON_EXTRACT_SCALAR（kv，'$ .key'）键，
 JSON_EXTRACT_SCALAR （kv，'$ .value'）值
 FROM UNNEST（SPLIT（labels，'}，{'））kv_temp，
 UNNEST（[CONCAT（'{'，REGEXP_REPLACE（kv_temp，r'^ \ [{||] $'，''），'}'）]）kv 
）标签，
的费用为
，来自`project.dataset.table` 
  

结果

 行描述labels.key labels.value成本
 1 aaa应用程序规模服务器0.323316 
部门hrd 
 2 bbb application2规模服务器2 0.342825 
 department2 hrd2 
  

I'm writing some queries with Google BigQuery. I want to extract the JSON from an array. Somehow I can extract it. And @Mikhail Berlyant helped me here. But now the problem is in extracting JSON from the array without Duplicates.

Current Structure:

I what I tried:

WITH
  cte AS (
  SELECT
    labels,
    cost
  FROM
    BILLING.gcp_billing_export_v1)
SELECT
  la,
  cost
FROM
  cte,
  UNNEST(labels) AS la

See the cost box, the COST value is repeated twice, because we have 2 KEY, VALUE pairs in the array.

So while doing sum(cost) with the group by la.key then I'm getting the wrong value.

What Im looking for is,

Can anyone help me with this?

解决方案

Below is for BigQuery Standard SQL

#standardSQL
SELECT 
  description, 
  ARRAY(
    SELECT AS STRUCT 
      JSON_EXTRACT_SCALAR(kv, '$.key') key, 
      JSON_EXTRACT_SCALAR(kv, '$.value') value 
    FROM UNNEST(SPLIT(labels, '},{')) kv_temp, 
    UNNEST([CONCAT('{', REGEXP_REPLACE(kv_temp, r'^\[{|}]$', ''), '}')]) kv
  ) labels,
  cost
FROM `project.dataset.table`   

You can test, play with above using excerpt of dummy data from your question as below

#standardSQL
WITH `project.dataset.table` AS (
  SELECT 'aaa' description, '[{"key":"application","value":"scaled-server"},{"key":"department","value":"hrd"}]' labels, 0.323316 cost UNION ALL
  SELECT 'bbb' description, '[{"key":"application2","value":"scaled-server2"},{"key":"department2","value":"hrd2"}]' labels, 0.342825 cost 
)
SELECT 
  description, 
  ARRAY(
    SELECT AS STRUCT 
      JSON_EXTRACT_SCALAR(kv, '$.key') key, 
      JSON_EXTRACT_SCALAR(kv, '$.value') value 
    FROM UNNEST(SPLIT(labels, '},{')) kv_temp, 
    UNNEST([CONCAT('{', REGEXP_REPLACE(kv_temp, r'^\[{|}]$', ''), '}')]) kv
  ) labels,
  cost
FROM `project.dataset.table`   

with result

Row description labels.key      labels.value    cost     
1   aaa         application     scaled-server   0.323316     
                department      hrd      
2   bbb         application2    scaled-server2  0.342825     
                department2     hrd2         

这篇关于SQL-从数组中提取JSON的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！