从 JSON 数组中提取每个值作为唯一行

Question

这是我的Presto查询

%jdbc(presto)
select
cast(json_extract(basicmetadata, '$.locales') as array<map<varchar, json>>)
from 
escherbird.entities
where
entityid = 1262415487625019394
and domainid = 124

它返回此数组

[{country="IN", hashCode=1828207720, _passthroughFields={}, language="en"}, {country="AU", hashCode=1899888409, _passthroughFields={}, language="en"}, {country="GB", hashCode=-2002878675, _passthroughFields={}, language="en"}, {country="IE", hashCode=-358794848, _passthroughFields={}, language="en"}, {country="CA", hashCode=392726027, _passthroughFields={}, language="en"}, {country="NZ", hashCode=-136386876, _passthroughFields={}, language="en"}, {country="US", hashCode=-1355251774, _passthroughFields={}, language="en"}]

此查询给我一个空结果

%jdbc(presto)
select
cast(json_extract(basicmetadata, '$.locales.country') as array<map<varchar, json>>)
from 
escherbird.entities
where
entityid = 1262415487625019394
and domainid = 124

如何编写查询以将每个国家/地区作为唯一行返回？

Answer 1

这是在SQL Server 2016及更高版本中运行的查询，

DECLARE @JSONDATA NVARCHAR(MAX);
SET 
@JSONDATA = N'{"domainId":29,"locales":[{"language":"en","country":"IN"},{"language":"en","country":"AU"}]}';
SELECT 
COUNTRY 
FROM 
OPENJSON(@JSONDATA) WITH (
LOCALES NVARCHAR(MAX) '$.locales' AS JSON
) CROSS APPLY OPENJSON(LOCALES, '$') WITH (
COUNTRY NVARCHAR(2) '$.country'
);

这是在Oracle 服务器中运行的查询

SELECT 
* 
FROM 
JSON_TABLE(
'{"domainId":29,"locales":[{"language":"en","country":"IN"},{"language":"en","country":"AU"}]}', 
'$.locales[*]' COLUMNS (
-- L_ROWNUM FOR ORDINALITY,
L_COUNTRY VARCHAR2(10) PATH '$.country'
)
)

Answer 2

如果您使用的是 Postgres，则可以使用以下内容：

select c.item ->> 'country' as country
from the_table t
cross join jsonb_array_elements(t.json_column -> 'locales') as c(item);

Answer 3

这是一个对象类型，如果你想在表上显示它，你应该把它转换为数组。

即：

var Array_name = Object.keys(object_name);

然后你可以把它用作数组