bigqueryでjson文字列から値を取り出す方法について質問

Question

前提・実現したいこと

bigqueryでstring形式の以下のようなカラムから文字列を抜き出したいです。

◆元の表

id
[{"id1":1111},{"id2":2222},{"id3":3333}]

◆文字列を抜き出して作りたい表

id1	id2	id3
1111	2222	3333

試したこと

以下のjson形式の場合は、

id
{"id1":"1111","id2":"2222","id3":"3333"}

下記のようにjson_extract_scalarを使用して抜き出すことができましたが、jsonの作りが違うため、nullで返ってきます。

  cast(json_extract_scalar(id, "$.id1") as int64)as id1,
  cast(json_extract_scalar(id, "$.id2") as int64)as id2,
  cast(json_extract_scalar(id, "$.id3") as int64)as id3

Answer 1

Javascript UDF の例です。

CREATE TEMPORARY FUNCTION my_parse_json(json_str STRING) 
RETURNS STRUCT<id1 INT64, id2 INT64, id3 INT64>
LANGUAGE js AS """
  var obj = {id1: 0, id2: 0, id3: 0};
  var json = JSON.parse(json_str);
  json.forEach(function(v){
    if ( v.hasOwnProperty('id1') ){ obj['id1'] = v.id1; }
    if ( v.hasOwnProperty('id2') ){ obj['id2'] = v.id2; }
    if ( v.hasOwnProperty('id3') ){ obj['id3'] = v.id3; }
   });
  return obj;
""";

SELECT my_parse_json('[{"id1":1111},{"id2":2222},{"id3":3333}]')

あるいは json_query_array を使った例です。

with x as (
  select * from 
  unnest(json_query_array('[{"id1":1111},{"id2":2222},{"id3":3333}]')) as j
)
select 
  (select json_extract_scalar(j, '$.id1') from x where json_extract_scalar(j, '$.id1') is not null) as id1,
  (select json_extract_scalar(j, '$.id2') from x where json_extract_scalar(j, '$.id2') is not null) as id2,
  (select json_extract_scalar(j, '$.id3') from x where json_extract_scalar(j, '$.id3') is not null) as id3

どっちも何か変な書き方になってしまいましたが、もっとスマートなやり方がありそうですね。

Comments

[HR_query]

ありがとうございます。
やはり、Javascript UDFか、json_query_arrayになりますかね。
シンプルにjson_extract_scalarやjson_extractを使用してできないかと、思ったのですが、
JSONPathの作り的に難しそうですね。
今後、要件整理するときに、こちら考慮できるので出すかります、ありがとうございました！

[68user]

array 要素数固定なら、
ifnull(json_extract_scalar(j, '$.[0].id1'), json_extract_scalar(j, '$.[1].id1'), json_extract_scalar(j, '$.[2].id1'))
などもアリかと思ったのですが、jsonpath における配列の表現がよくわからずでできませんでした。

[HR_query]

ありがとうございます。
ifnullは試してなかったので実行してみたのですが、
---------------
Invalid token in JSONPath at: .[0]
---------------
エラーでうまくいかないですね。

>jsonpath における配列の表現
まさにここなんですが、何かしら方法が見つかったら追記したいとおもいます。