解析没有键名的JSON以检索列

Question

我从data.gov加载json，它没有json数据中的值的键名，例如:元数据是单独可用的。

我能够将json加载到一个变体列中，但无法看到如何解析和查询特定列，例如下面的Frankford --我尝试了JSONcol:data，它返回整个条目，但是无法看到如何指定第4列。

  {
    data: [ [ "row-ea6u~fkaa~32ry", "0B8F94EE5292", 0, 1486063689, null, 1486063689, null, "{ }", "410", "21206", "Frankford", "2", "NORTHEASTERN", [ "{\"address\": \"4509 BELAIR ROAD\", \"city\": \"Baltimore\", \"state\": \"MD\", \"zip\": \"\"}", null, null, null, true ], null, null, null ]]
    }

以下代码用于创建和加载雪花表：

create or replace table snowpipe.public.snowtable(jsontext variant);

copy into snowpipe.public.snowtable
    from @snowpipe.public.snowstage
    file_format = (type = 'JSON')

Answer 1

不完全确定加载了变量数据后，它的外观如何，但通过PARSE_JSON为您的对象进行变体实验。我必须将\双斜杠才能使其有效。

select 
    PARSE_JSON('{ data: [ [ "row-ea6u~fkaa~32ry", "0B8F94EE5292", 0, 1486063689, null, 1486063689, null, "{ }", "410", "21206", "Frankford", "2", "NORTHEASTERN", [ "{\\"address\\": \\"4509 BELAIR ROAD\\", \\"city\\": \\"Baltimore\\", \\"state\\": \\"MD\\", \\"zip\\": \\"\\"}", null, null, null, true ], null, null, null ]]}') as j
    ,j:data as jd
    ,jd[0] as jd0
    ,jd0[3] as jd0_3
    ,array_slice(j:data[0],3,5) as jd0_3to4
;

显示可以使用[0]表示法来索引数组，从而获得结果：

J: { "data": [ [ "row-ea6u~fkaa~32ry", "0B8F94EE5292", 0, 1486063689, null, 1486063689, null, "{ }", "410", "21206", "Frankford", "2", "NORTHEASTERN", [ "{\"a...

JD: [ [ "row-ea6u~fkaa~32ry", "0B8F94EE5292", 0, 1486063689, null, 1486063689, null, "{ }", "410", "21206", "Frankford", "2", "NORTHEASTERN", [ "{\"address\": \"4509 BELAIR ROAD\", \"city\": \"...

JD0: [ "row-ea6u~fkaa~32ry", "0B8F94EE5292", 0, 1486063689, null, 1486063689, null, "{ }", "410", "21206", "Frankford", "2", "NORTHEASTERN", [ "{\"address\": \"4509 BELAIR ROAD\", \"city\": \"Baltimore\", \"state\": \"MD\", \"...

JD0_3: 1486063689

JD0_3TO4: [ 1486063689, null ]

因此，如果要访问的数据中有未知数量的第一级元素，请使用LATERAL FLATTEN，如下所示：

WITH data as (
    select PARSE_JSON('{ data: [ [ "row-1", "0B8", 0 ],["row-2", "F94", 2], 
["row-3", "EE5", 4]]}') as j
)
select f.value[0]::text as row_name
    ,f.value[1]::text as serial_number
    ,f.value[2]::number as num
from data d,
lateral flatten(input=> d.j:data) f;

给予：

 ROW_NAME   SERIAL_NUMBER   NUM
 row-1      0B8             0
 row-2      F94             2
 row-3      EE5             4