概要

Amazon S3 SelectでJSONデータをクエリした際に指定要素が無い場合の挙動で少しハマったのでメモしておきます。

現象再現

ソースデータ

下記のようなJSONテキストファイルを準備します。
1つ目のオブジェクトは attr.age要素あり、2つ目のオブジェクトはありません。

source.json

{
  "id": 101,
  "name": "taro",
  "attr": {
    "region": "Japan",
    "age": 30
  }
}
{
  "id": 102,
  "name": "hanako",
  "attr": {
    "region": "United States"
  }
}

S3へアップロード

$ aws s3 cp source.json  s3://my-bucket/

S3 Select

ソースはJSON、出力はCSVとします。
出力CSVはすべての行について下記の4列となることを期待しています

列位置	想定出力列名	JSON上の表記
1	id	id
2	name	name
3	age	attr.age
4	region	attr.region

$ aws s3api select-object-content --bucket my-bucket --key source.json \
--expression-type SQL \
--expression "SELECT s.id, s.name, s.attr.age, s.attr.region FROM s3object s" \
--input-serialization '{"JSON": {"Type": "LINES"}}' \
--output-serialization '{"CSV": {}}' \
outfile.csv

出力を確認

1行目は期待通りですが、2行目がNGです。3列目 age 列が抜けています。
確かにソースJSONには attr.age要素が無いのですが、これだと列数が不一致になってしまいます。

$ cat outfile.csv
101,taro,30,Japan
102,hanako,United States

考察

出力をJSONにすると age 要素なしで出力されます。JSONとしてはこれで正しいですね。
ただ、CSVの場合は行ごとに列の数が一致しないのは多くの場合期待されないと思います。

$ aws s3api select-object-content --bucket my-bucket --key source.json \
--expression-type SQL \
--expression "SELECT s.id, s.name, s.attr.age, s.attr.region FROM s3object s" \
--input-serialization '{"JSON": {"Type": "LINES"}}' \
--output-serialization '{"JSON": {}}' \
outfile.json

$ cat outfile.json
{"id":101,"name":"taro","age":30,"region":"Japan"}
{"id":102,"name":"hanako","region":"United States"}

対応方法

SQL関数 COALESCE を使用し、null出力を明示します。
これで値なしの列も出力されます。
（処理として適切な値があればnullではなくても良いと思います。たとえば「999＝年齢不明を示す仕様」の場合は999が良いかもしれません）

$ aws s3api select-object-content --bucket my-bucket --key source.json \
--expression-type SQL \
--expression "SELECT s.id, s.name, COALESCE(s.attr.age, null) age, s.attr.region FROM s3object s" \
--input-serialization '{"JSON": {"Type": "LINES"}}' \
--output-serialization '{"CSV": {}}' \
outfile.csv

$ cat outfile.csv
101,taro,30,Japan
102,hanako,,United States

参考:JSON出力時

JSON出力にした場合も要素数が一致するようになります。

$ aws s3api select-object-content --bucket my-bucket --key source.json \
--expression-type SQL \
--expression "SELECT s.id, s.name, COALESCE(s.attr.age, null) age, s.attr.region FROM s3object s" \
--input-serialization '{"JSON": {"Type": "LINES"}}' \
--output-serialization '{"JSON": {}}' \
outfile.json

$ cat outfile.json
{"id":101,"name":"taro","age":30,"region":"Japan"}
{"id":102,"name":"hanako","age":null,"region":"United States"}

参照ドキュメント

https://docs.aws.amazon.com/AmazonS3/latest/userguide/s3-glacier-select-sql-reference-select.html