Sql 雅典娜嵌套Json提取液

发布于01月19日

我在雅典娜有一个嵌套的json

 { "template": {"name":"User Name", "id": 10001}} 

Output :- {"name":"User Name", "id": 10001}

我try 从内部json中提取id到目前为止，我已经try 了json提取和json提取标量，但两者都不适用于嵌套的JSON

有了这个，我们就可以访问模板json

JSON_EXTRACT(db.column_name, '$.template') AS template

我正在try 嵌套的json来访问id字段，但它无法访问

json_extract_scalar(json_extract(db.column_name, '$.template'), '$.id') as id
json_extract(json_extract(db.column_name, '$.template'), '$.id') as id

推荐答案

Tbh您的两次try 对我都有效，但通常您应该能够通过提供完整路径访问它(不需要嵌套提取):

-- sample data
with dataset(json_col) as(
    values ('{ "template": {"name":"User Name", "id": 10001}}' )
)

-- query
select json_extract_scalar(json_col, '$.template.id') id
from dataset;

输出:

  id
-------
 10001
(1 row)

您可能有一些错误的行，因此您可以使用try，它将在出现错误时返回NULL:

-- query
select try(json_extract_scalar(json_col, '$.template.id')) id
from dataset;

UPD个

实际的问题是template包含一个字符串，而不是一个嵌套的JSON对象(字符串的内容实际上是编码的JSON).您需要再次解析JSON-try 在内部使用json_extract_scalar(json_extract_scalar将返回varchar字符串，外部将再次解析):

-- sample data
with dataset(json_col) as(
    values ('{ "template": "{\"project_id\": 1736024, \"sender_name\": \"something\"}"}' )
)

-- query
select json_extract_scalar(json_extract_scalar(json_col, '$.template'), '$.project_id') id
from dataset;