JSON 数据
使用 FJSONPARSER 加载 JSON 格式的数据。
JSON 数据的架构是属性/值对中的一组属性名称。将 JSON 数据加载到列式表或 Flex 表中的实体化列中时，数据中的属性名称必须与表中的列名称匹配。您不需要加载数据中的所有列。
JSON 解析器可以将数据加载到任何标量类型、强类型复杂类型或可变复杂类型的列中。可变复杂类型意味着您没有完全指定该列的架构。可以将表中的这些列定义为 LONG VARBINARY，并且可以使用 Flex 函数从中提取值。
在 COPY 语句中，使用 PARSER 参数可指定 JSON 解析器，如以下示例所示：
=> CREATE EXTERNAL TABLE customers(id INT, address VARCHAR, transactions ARRAY[INT,10])
AS COPY FROM 'cust.json' PARSER FJSONPARSER();
此解析器有几个可选参数，其中一些专门用于 Flex 表和可变复杂类型。
在加载 JSON 数据之前，请考虑使用 JSONLint 等工具来验证数据是否有效。
如果将 JSON 数据加载到 Flex 表中，Vertica 会将所有数据加载到 __raw__ (VMap) 列中，包括在数据中找到的复杂类型。可以使用 Flex 函数来提取值。
强类型复杂类型
JSON 数据可能包含数组、结构体以及这两者的组合。可以将这些数据作为可变 (VMap) 列或具有强类型的可变列来加载。强类型允许您直接查询值，而无需使用函数来解压 VMap 列。
=> CREATE EXTERNAL TABLE rest
(name VARCHAR, cuisine VARCHAR,
location_city ARRAY[VARCHAR(80),50],
menu ARRAY[ ROW(item VARCHAR(80), price FLOAT), 100 ]
)
AS COPY FROM :restdata PARSER FJSONPARSER();
=> SELECT name, location_city, menu FROM rest;
name | location_city | menu
-------------------+----------------------------+------------------------------------------------------------------------------
Bob's pizzeria | ["Cambridge","Pittsburgh"] | [{"item":"cheese pizza","price":8.25},{"item":"spinach pizza","price":10.5}]
Bakersfield Tacos | ["Pittsburgh"] | [{"item":"veggie taco","price":9.95},{"item":"steak taco","price":10.95}]
(2 rows)
将 JSON 数据加载到具有复杂类型的强类型的表中时，Vertica 会忽略解析器的 flatten_maps 和 flatten_arrays 参数。
强复杂类型和可变复杂类型
强类型的优点是在查询中访问数据更容易（且更高效）。缺点是会忽略在数据中找到但未包含在列定义中的其他值。如果此数据中的菜单结构体包含更多属性（例如卡路里），则不会加载它们，因为列的定义仅指定了选项和价格。以下示例使用可变复杂类型来显示额外的属性：
=> CREATE EXTERNAL TABLE rest
(name VARCHAR, cuisine VARCHAR,
location_city LONG VARBINARY, menu LONG VARBINARY)
AS COPY FROM :restdata
PARSER FJSONPARSER(flatten_maps=false);
=> SELECT name, MAPTOSTRING(location_city) as location_city, MAPTOSTRING(menu) AS menu FROM rest;
name | location_city | menu
-------------------+--------------------------+---------------------------------------------------
Bob's pizzeria | {
"0": "Cambridge",
"1": "Pittsburgh"
} | {
"0": {
"calories": "1200",
"item": "cheese pizza",
"price": "8.25"
},
"1": {
"calories": "900",
"item": "spinach pizza",
"price": "10.50"
}
}
Bakersfield Tacos | {
"0": "Pittsburgh"
} | {
"0": {
"item": "veggie taco",
"price": "9.95",
"vegetarian": "true"
},
"1": {
"item": "steak taco",
"price": "10.95"
}
}
(2 rows)
从特定的起点加载
您无需加载整个 JSON 文件。您可以使用 start_point 参数在特定键而非文件开头处加载数据。数据解析始于 start_point 键后，直到文件的末尾或首个 start_point 值的末尾。解析器将忽略 start_point 的任何后续实例，即使该键在输入文件中多次出现也是如此。如果输入数据只包含 start_point 键的一个副本，并且该值为 JSON 元素的列表，解析器会将列表中的各个元素加载为行。
如果 start_point 值在 JSON 数据中出现多次，您可以使用 start_point_occurrence 整数参数来指定在哪次出现时开始解析。
此示例使用以下 JSON 数据，这些数据已保存到名为 alphanums.json 的文件中：
{ "A": { "B": { "C": [ { "d": 1, "e": 2, "f": 3 }, { "g": 4, "h": 5, "i": 6 },
{ "j": 7, "k": 8, "l": 9 } ] } } }
将此数据加载到 Flex 表中会产生以下结果：
=> CREATE FLEX TABLE start_json;
CREATE TABLE
=> COPY start_json FROM '/home/dbadmin/data/alphanums.json' PARSER FJSONPARSER();
Rows Loaded
-------------
1
(1 row)
=> SELECT maptostring(__raw__) FROM start_json;
maptostring
-------------------------------------------------------------------------------
{
"A.B.C" : {
"0.d" : "1",
"0.e" : "2",
"0.f" : "3",
"1.g" : "4",
"1.h" : "5",
"1.i" : "6",
"2.j" : "7",
"2.k" : "8",
"2.l" : "9"
}
}
(1 row)
以下加载指定了起点：
=> TRUNCATE TABLE start_json;
TRUNCATE TABLE
=> COPY start_json FROM '/home/dbadmin/data/alphanums.json' PARSER FJSONPARSER(start_point='B');
Rows Loaded
-------------
1
(1 row)
=> SELECT maptostring(__raw__) FROM start_json;
maptostring
--------------------------------------------------------------------------------
{
"C" : {
"0.d" : "1",
"0.e" : "2",
"0.f" : "3",
"1.g" : "4",
"1.h" : "5",
"1.i" : "6",
"2.j" : "7",
"2.k" : "8",
"2.l" : "9"
}
}
(1 row)
处理无效的 JSON 记录
如果 JSON 数据包含语法错误，您的加载可能会因无效记录而失败。如果 JSON 记录始终用换行符等字符进行分隔，则可以在 COPY 语句中使用 RECORD_TERMINATOR 选项来跳过这些无效的记录。设置记录终止符允许解析器跳过无效记录并继续解析剩余数据。
如果标记记录所用的字符不一致，则可以使用 ERROR TOLERANCE 选项。ERROR TOLERANCE 会跳过包含无效 JSON 记录的整个源文件，而 RECORD_TERMINATOR 会跳过个别格式错误的 JSON 记录。您可以同时使用这两个选项。
以下示例将使用无效记录：
=> => CREATE FLEX TABLE fruits();
CREATE TABLE
=> COPY fruits FROM STDIN PARSER FJSONPARSER(RECORD_TERMINATOR=E'\n');
Enter data to be copied followed by a newline.
End with a backslash and a period on a line by itself
>> {"name": "orange", "type": "fruit", "color": "orange", "rating": 5 }
>> {"name": "apple", "type": "fruit", "color": "green" }
>> {"name": "blueberry", "type": "fruit", "color": "blue", "rating": 10 }
>> "type": "fruit", "rating": 7 }
>> {"name": "banana", "type" : "fruit", "color": "yellow", "rating": 3 }
>> \.
使用 MAPTOSTRING 查看 Flex 表，以确认在成功加载剩余记录时跳过了无效记录：
=> SELECT MAPTOSTRING(__raw__) FROM fruits;
maptostring
--------------------------------------------------------------------------------------------
{
"color" : "orange",
"name" : "orange",
"rating" : "5",
"type" : "fruit"
}
{
"color" : "green",
"name" : "apple",
"type" : "fruit"
}
{
"color" : "blue",
"name" : "blueberry",
"rating" : "10",
"type" : "fruit"
}
{
"color" : "yellow",
"name" : "banana",
"rating" : "3",
"type" : "fruit"
}
(4 rows)
拒绝包含实体化列类型错误的数据
默认情况下，如果 FJSONPARSER 无法将数据值强制转换为与列定义匹配的类型，它会将值设置为 NULL。您可以选择使用 reject_on_materialized_type_error 参数来拒绝这些值。如果此参数为 true，则 COPY 拒绝此类行并报告错误。
如果该列是强类型复杂类型，而不是可变复杂类型，则复杂类型中的类型不匹配将导致整个列被视为不匹配。解析器不会部分加载复杂类型；如果无法强制使用任何 ROW 字段或 ARRAY 元素，则会为列加载 NULL。
以下示例会尝试加载无效数据。请注意，查询结果中缺少无效行：
=> CREATE TABLE test(one VARCHAR, two INT);
CREATE TABLE
=> COPY test FROM stdin
PARSER FJSONPARSER(reject_on_materialized_type_error=true);
Enter data to be copied followed by a newline.
End with a backslash and a period on a line by itself.
>> {"one": 1, "two": 2}
>> {"one": "one", "two": "two"}
>> {"one": "one", "two": 2}
>> \.
=> SELECT one, two FROM test;
one | two
-----+-----
1 | 2
one | 2
(2 rows)
拒绝或忽略 Flex 表中的空键
有效的 JSON 文件可能包含空键值对。默认情况下，对于 Flex 表，FJSONPARSER 会加载空键值对，如以下示例所示：
=> CREATE FLEX TABLE fruits();
CREATE TABLE
=> COPY fruits FROM STDIN PARSER FJSONPARSER();
Enter data to be copied followed by a newline.
End with a backslash and a period on a line by itself.
>> {"name": "orange", "rating": 5}
>> {"name": "apple", "rating" : 10}
>> {"": "banana", "rating" : 2}
>> \.
=> SELECT MAPTOSTRING(__raw__) FROM fruits;
maptostring
---------------------------------------
{
"name": "orange",
"rating": "5"
}
{
"name": "apple",
"rating": "10"
}
{
"": "banana",
"rating": "2"
}
(3 rows)
要忽略具有空白键的字段，请使用 omit_empty_keys 参数：
=> COPY fruits FROM STDIN PARSER FJSONPARSER(omit_empty_keys=true);
Enter data to be copied followed by a newline.
End with a backslash and a period on a line by itself.
>> {"name": "apple", "rating": 5}
>> {"": "missing", "rating" : 1}
>> {"name": "", "rating" : 3}
>> \.
=> SELECT MAPTOSTRING(__raw__) FROM fruits;
maptostring
--------------------------------------
{
"name": "apple",
"rating": "5"
}
{
"rating": "1"
}
{
"name": "",
"rating": "3"
}
(3 rows)
请注意，缺少名称的第二个值仍会与另一个（非空）字段一起加载。要完全拒绝该行，请使用 reject_on_empty_key 参数：
=> COPY fruits FROM STDIN PARSER FJSONPARSER(reject_on_empty_key=true);
Enter data to be copied followed by a newline.
End with a backslash and a period on a line by itself.
>> {"name" : "apple", "rating" : 5}
>> {"" : "missing", "rating" : 1}
>> {"name" : "", "rating" : 3}
>> \.
=> SELECT MAPTOSTRING(__raw__) FROM fruits;
maptostring
--------------------------------------
{
"name": "apple",
"rating": "5"
}
{
"name": "",
"rating": "3"
}
(2 rows)