如何使用AWS Athena -Presto从嵌套JSON中的特定字段中提取数据

Question

我的jsons在S3桶中以下面的格式使用，我正在尝试仅提取" id"，" label"＆amp;使用雅典娜的"字段"键的"值"。我尝试了数组图，但没有成功。另外，在"值"字段上 - 我希望将内容捕获为简单的文本，忽略其中的任何列表/字典。

我也不想为这些JSON创建任何Hive模式，并在可能的情况下寻找Presto SQL解决方案。

{  
    "reports":{  
        "client":{  
            "pdf":"https://reports.s3-accelerate.amazonaws.com/looks/123/reports/client.pdf",
            "html":"https://api.com/looks/123/reports/client.html"
        },
        "public":{  
            "pdf":"https://s3.amazonaws.com/reports.com/looks/123/reports/public.pdf",
            "html":"https://api.look.com/looks/123/reports/public.html"
        }
    },
    "actors":{  
        "looker":{  
            "firstName":"Rosa",
            "lastName":"Mart"
        },
        "client":{  
            "email":"[e-mail]",
            "firstName":"XXX",
            "lastName":"XXX"
        }
    },
    "_id":"123",
    "fields":[  
                {  
        "id":"fence_condition_missing_sections",
        "context":[  
            "Fence Condition"
        ],
        "label":"Missing Sections",
        "type":"choice",
        "value":"None"
    },
        {  
            "id":"photos_landscaped_area",
            "context":[  
                "Landscaping Photos"
            ],
            "label":"Landscaped Area",
            "type":"photo-with-description",
            "value":[  
                {  
                    "description":"Front",
                    "photo":"https://reports-wegolook-com.s3-accelerate.amazonaws.com/looks/123/looker/1.jpg"
                },
                {  
                    "description":"Front entrance ",
                    "photo":"https://reports-wegolook-com.s3-accelerate.amazonaws.com/looks/123/looker/2.jpg"
                }
            ]
        }
    ],
    "jobNumber":"xxx",
    "createdAt":"2018-10-11T22:39:37.223Z",
    "completedAt":"2018-01-27T20:13:49.937Z",
    "inspectedAt":"2018-01-21T23:33:48.718Z",
    "type":"ZZZ-commercial",
    "name":"Commercial"
}'

预期输出：

--------------------------------------------------------------------------------
| ID     | LABEL |  VALUE                                                  | 
--------------------------------------------------------------------------------
|   photos_landscaped_area     |  Landscaped Area |  [{"description":"Front",...}]    |
----------------------------------------------------------------------------
| fence_condition_missing_sections | Missing Sections | None|
----------------------------------------------------------------------------

Answer 1

我将假设您的数据是按单行的一行格式，并且您提供了一个格式化的示例，以解决可读性。如果这是不正确的，请参阅hive中的多行JSON文件查询的问题。

当JSON文档的模式不完全规则时，您可以将该列创建为string列并使用JSON_*函数从中提取值。

首先，您需要为原始数据创建一个表：

CREATE TABLE data (
  fields array<struct<id:string,label:string,value:string>>
)
ROW FORMAT SERDE 'org.openx.data.jsonserde.JsonSerDe'
LOCATION 's3://…'

（如果您对JSON文档中的其他字段不感兴趣，则可以在创建表时忽略这些字段）

然后创建一个使数据变平的视图：

CREATE VIEW flat_data AS
SELECT
  field.id,
  field.label,
  field.value
FROM data
CROSS JOIN UNNEST(fields) AS f(field)

从此视图中选择应为您提供所需的结果。

我怀疑您还在寻找如何从values结构中提取属性，这是我上面提到的：

SELECT
  label,
  JSON_EXTRACT(value, '$.photo') AS photo_urls
FROM flat_data
WHERE id = 'photos_landscaped_area'

查看所有可用JSON功能的Presto文档。