在“数据库”中查询半结构化数据
请注意
在Databricks Runtime 8.1及以上版本中可用。
本文介绍了Databricks SQL操作符，可用于查询和转换存储为JSON的半结构化数据。
请注意
该特性允许您读取半结构化数据，而无需将文件平整化。但是，为了获得最佳的读查询性能，Databricks建议使用正确的数据类型提取嵌套列。
您可以使用以下语法从包含JSON字符串的字段中提取列<列名称>:< extraction-path >,在那里<列名称>字符串是列名和< extraction-path >要提取的字段的路径。返回的结果是字符串。
创建一个包含高度嵌套数据的表
运行以下查询以创建具有高度嵌套数据的表。本文中的示例都引用了这个表。
创建表格store_data作为选择”{“存储”:{“水果”:[{“重量”:8,“类型”:“苹果”},{“重量”:9,“类型”:“梨”}),“篮子”:((1、2、{“b”:“y”,“一个”:“x”}),(3、4),(5、6)),“书”:(｛奈杰尔•里斯”“作者”:“标题:“世纪名言”，“类别”:“引用”,“价格”:8.95}，｛“作者”:“赫尔曼·麦尔维尔”,“标题”:“白鲸记”,“类别”:“小说”,“价格”:8.99,“isbn”:“0-553-21311-3”}，｛“作者”:“J。r。r。托尔金"“标题”:“指环王”，“类别”:“小说”,“读者”:({“年龄”:25岁的“名称”:“bob”},{“年龄”:26日,“名字”:“杰克”}),“价格”:22.99,“isbn”:“0-395-19395-8”｝),“自行车”:{“价格”:19.95,“颜色”:“红色”｝}，“老板”:“艾米”,“邮政编码”:“94025”,“fb: testid”:“1234”} '作为生
提取顶级列
要提取列，请在提取路径中指定JSON字段的名称。
可以在括号内提供列名。括号内引用的列是匹配的大小写敏感。列名的引用也不区分大小写。
选择生：老板，生：老板从store_data
+-------+-------+|老板|老板|+-------+-------+|艾米|艾米|+-------+-------+
当你使用括号时引用是区分大小写的选择生：老板case_insensitive，生：[“主人”］case_sensitive从store_data
+------------------+----------------+|case_insensitive|case_sensitive|+------------------+----------------+|艾米|零|+------------------+----------------+
使用反引号转义空格和特殊字符。字段名以大小写匹配如果不。
—使用反引号转义特殊字符。使用反勾号时引用不区分大小写。使用括号区分大小写。选择生：｀邮政编码代码｀，生：｀邮政编码代码｀，生：[“fb: testid”］从store_data
+----------+----------+-----------+|邮政编码代码|邮政编码代码|神奇动物：testid|+----------+----------+-----------+|94025|94025|1234|+----------+----------+-----------+
请注意
如果由于不区分大小写的匹配，JSON记录包含多个可以匹配提取路径的列，那么您将收到一个错误，要求您使用括号。如果行与列之间存在匹配，则不会收到任何错误。以下语句将抛出一个错误:{“foo”:“酒吧”,“Foo”:“酒吧”}，下面的语句不会抛出错误:
｛“foo”：“酒吧”｝｛“Foo”：“酒吧”｝
提取嵌套字段
通过点表示法或使用括号指定嵌套字段。当您使用方括号时，列是区分大小写匹配的。
使用点表示法选择生：商店。自行车从store_data——返回的列是一个字符串
+------------------+|自行车|+------------------+|｛||“价格”：19.95，||“颜色”：“红色”||｝|+------------------+
——使用括号选择生：商店［“自行车”),生：商店［“自行车”］从store_data
+------------------+---------+|自行车|自行车|+------------------+---------+|｛|零||“价格”：19.95，|||“颜色”：“红色”|||｝||+------------------+---------+
从数组中提取值
用括号为数组中的元素建立索引。索引以0为基础。你可以使用星号(＊)，然后用点或括号符号从数组中的所有元素中提取子字段。
——索引元素选择生：商店。水果［0),生：商店。水果［1］从store_data
+------------------+-----------------+|水果|水果|+------------------+-----------------+|｛|｛||“重量”：8，|“重量”：9，||“类型”：“苹果”|“类型”：“梨”||｝|｝|+------------------+-----------------+
——从数组中提取子字段选择生：商店。书［＊］.国际标准图书编号从store_data
+--------------------+|国际标准图书编号|+--------------------+|［||零，||“0-553-21311-3”，||“0-395-19395-8”||］|+--------------------+
——访问数组中的数组或数组中的结构选择生：商店。篮子［＊),生：商店。篮子［＊] [0］first_of_baskets，生：商店。篮子［0] [＊］first_basket，生：商店。篮子［＊] [＊］all_elements_flattened，生：商店。篮子［0] [2］.b子域从store_data
+----------------------------+------------------+---------------------+---------------------------------+----------+|篮子|first_of_baskets|first_basket|all_elements_flattened|子域|+----------------------------+------------------+---------------------+---------------------------------+----------+|［|［|［|［1，2, {“b”：“y”，“一个”：“x”}，3.，4，5，6］|y||［1，2, {“b”：“y”，“一个”：“x”}),|1，|1，||||［3.，4),|3.，|2，||||［5，6］|5|｛“b”：“y”，“一个”：“x”｝||||］|］|］|||+----------------------------+------------------+---------------------+---------------------------------+----------+
把值
你可以使用：：将值转换为基本数据类型。使用from_json方法将嵌套结果强制转换为更复杂的数据类型，如数组或结构。
——price返回double类型，而不是string类型选择生：商店。自行车。价格：：双从store_data
+------------------+|价格|+------------------+|19.95|+------------------+
——使用from_json转换为更复杂的类型选择from_json（生：商店。自行车，'价格双倍，颜色字符串'）自行车从store_data——返回的列是一个包含列price和color的结构体
+------------------+|自行车|+------------------+|｛||“价格”：19.95，||“颜色”：“红色”||｝|+------------------+
选择from_json（生：商店。篮子［＊),“数组<数组<字符串> >”）篮子从store_data——返回的列是字符串数组的数组
+------------------------------------------+|篮子|+------------------------------------------+|［||［“1”，“2”，“{\”b\”：\”y\”，\”一个\”：\”x\”}]”，||［“3”，“4”),||［“5”，“6”］||］|+------------------------------------------+
空的行为
的JSON字段存在时零值，您将收到一个SQL零值，而不是零文本值。
选择”{零}“关键”:“：关键是零sql_null，”{零}“关键”:“：关键= =“零”text_null
+-------------+-----------+|sql_null|text_null|+-------------+-----------+|真正的|零|+-------------+-----------+
使用Spark SQL操作符转换嵌套数据
Apache Spark有许多用于处理复杂和嵌套数据的内置函数。下面的笔记本包含了一些例子。
此外,高阶函数当内置Spark操作符不能以您想要的方式转换数据时，提供许多额外的选项。