你似乎非常接近了，其中一个选项是对未嵌套的值使用json提取函数（例如json_extract_scalar）（在转换为map之后，请注意unnest的简洁语法）：

-- sample data
with dataset (users) as (
    values ('{
        "[email protected]":[1.0,5.95],
        "[email protected]":[2.0,30.733],
        "[email protected]":[1.0,4.433],
        "[email protected]":[2.0,16.25]
    }')
)

-- query
select k splitUsers,
    json_extract_scalar(v, '$[0]') firstValue,
    json_extract_scalar(v, '$[1]') secondValue
from dataset
, unnest (cast(json_parse(users) as map(varchar, json))) as t(k,v)
;

字符串
输出量：
| splitUsers| firstValue| secondValue|
| --|--|--|
| email protected(https://stackoverflow.com/cdn-cgi/l/email-protection)|2.0版本|十六点二十五分|
| email protected(https://stackoverflow.com/cdn-cgi/l/email-protection)的|2.0版本|30.733|
| email protected(https://stackoverflow.com/cdn-cgi/l/email-protection)的|1.0版|四点四三三|
| email protected(https://stackoverflow.com/cdn-cgi/l/email-protection)的|1.0版|五点九五|

您需要首先将Map解嵌套到单独的行中，然后从JSON数组中提取

SELECT
  tn.*,
  t.splitUsers,
  json_extract_scalar(t.vals, '$[0]') AS firstValue,
  json_extract_scalar(t.vals, '$[1]') AS secondValue
from tableName AS tn
CROSS JOIN UNNEST(CAST(json_parse(users) AS MAP(VARCHAR, JSON))) AS t (splitUsers, vals);

字符串

这里有一个基于以前答案的版本，但避免了使用json_extract_scalar。
它依赖于这样一个事实：在特里诺中，您可以将JSON对象转换为SQLMap，将JSON数组转换为SQL行：CAST(json_parse(users) AS map(varchar, row(double, double)))

WITH dataset(users) AS (
    VALUES ('{
        "[email protected]":[1.0,5.95],
        "[email protected]":[2.0,30.733],
        "[email protected]":[1.0,4.433],
        "[email protected]":[2.0,16.25]
    }')
)

SELECT
    k AS splitUsers,
    v[1] AS firstValue,
    v[2] AS secondValue
FROM 
    dataset, 
    UNNEST(cast(json_parse(users) as map(varchar, row(double, double)))) as t(k,v)

字符串
在最新版本的特里诺中，您可以使用JSON_TABLE直接处理数据并提取字段，而无需调用json_parse或将JSON值转换为SQL类型：

WITH dataset(users) AS ( ... )
SELECT t.*
FROM dataset, JSON_TABLE(users, 'strict $.keyvalue()' COLUMNS (
    splitUsers VARCHAR PATH 'strict $.name',
    firstValue DOUBLE PATH 'strict $.value[0]',
    secondValue DOUBLE PATH 'strict $.value[1]'
)) AS t

型