Bigquery: Is there a json path way to extract only values from a json array with dynamic keys?-CodePudding

The specification is as follows.

The field value is a json array.
Each json element has a dynamic key.
Extract all the values of the array

[Sample]

WITH TempTable AS
 (SELECT '1' as id, '[{"1_03":8},{"1_04":8}]' as target  UNION ALL
  SELECT '2', '[{"1_19":8},{"1_08":9}]' UNION ALL
  SELECT '3', '[{"1_23":4},{"1_24":6}]'
  )
SELECT 
    id,
    json_query_array(target,'$'),
    REGEXP_EXTRACT_ALL(target, '"(.*?)":') as key,
    REGEXP_EXTRACT_ALL(target, ':([0-9] )') as val
FROM TempTable

For fields with json array (in the example), I could only extract the value with regular expression.

But, is there a way to apply the json path method?

CodePudding user response：

Consider below approach

SELECT id, element,
  kv[OFFSET(0)] as key,
  kv[OFFSET(1)] as value
FROM TempTable, 
UNNEST(JSON_EXTRACT_ARRAY(target)) element,
UNNEST([STRUCT(SPLIT(TRANSLATE(element, '{}"', ''), ':') as kv)])

if applied to sample data in your question - output is

In case if you want to preserve rows - use below version

SELECT id, ARRAY( 
    SELECT AS STRUCT
      element,
      kv[OFFSET(0)] as key,
      kv[OFFSET(1)] as value,    
    FROM UNNEST(JSON_EXTRACT_ARRAY(target)) element,
    UNNEST([STRUCT(SPLIT(TRANSLATE(element, '{}"', ''), ':') as kv)]) 
  ) AS output
FROM TempTable

In this case - output is