如何从Zeppelin SQL中提取数据JSON [英] how to extract data JSON from zeppelin sql

问题描述

我查询齐柏林飞艇上的test_tbl表.表数据结构如下所示:

I query to test_tbl table on Zeppelin. the table data structure looks like as below :

%sql
desc stg.test_tbl
col_name | data_type | comment
id       |  string   |
title    |  string   |
tags     |  string   |

标签列具有数据JSON类型，如下所示:

The tags column has data JSON type following as :

{"name":[{"family":null，"first":"nelson"}，{"pos_code":{"house":"tlv"，"id":"A12YR"}}}}}

{"name":[{"family": null, "first": "nelson"}, {"pos_code":{"house":"tlv", "id":"A12YR"}}]}

并且我想查看带有列的JSON数据，所以我的查询是:

and I want to see the JSON data with columns, so my query is :

select *, tag.*
from stg.test_tbl as t
lateral view explode(t.tags.name) name as name
lateral view explode(name.pos_code) pos_code as pos_code

但是当我查询时，它返回

but when I query, it returns

Can't extract value from tags#3423: need struct type but got string; line 3 pos 21
set zeppelin.spark.sql.stacktrace = true to see full stacktrace

我应该在where语句中以字符串查询吗?

should i query as string in where statement?

推荐答案

您可以使用JSON字符串类型的get_json_object.另外，如果JSON是数组类型

You can use get_json_object in string type of JSON. Also, if the JSON is an array type as

{"name":[{"family": null, "first": "nelson"}, {"pos_code":{"house":"tlv", "id":"A12YR"}}]}

，您可以按

select * from stg.test_tbl as t
where t.pos_code[0].house = "tlv"

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