PostgreSQL中如何将值正确用于JSON操作符#>右侧？

现有表emodels包含JSON类型字段rights，示例内容如下：

{
  "library": {
    "media": true,
    "firmware": true,
    "gimages": true,
    "label": true,
    "ui": true
  },
  "reboot": true
}

初始SQL可正常执行：

select
    e.id,
    e.name,
    "right"
from
    emodels e,
    lateral (select 'media' as subfolder) subfolder,
    lateral (select '{library,'||(subfolder::text)||'}' as "right") "right"

但尝试用以下SQL提取JSON值时：

select
    e.id,
    e.name,
    rights #> "right" as allow
from
    emodels e,
    lateral (select 'media' as subfolder) subfolder,
    lateral (select '{library,'||(subfolder::text)||'}' as "right") "right"

出现错误：SQL Error [42883]: ERROR: operator does not exist: json #> text，即使显式转换为JSON（rights #> cast("right" as json) as allow）仍报类似错误，如何正确转换类型使JSON提取生效？

• 问题核心：PostgreSQL的#> JSON操作符要求右侧参数为**text[]类型的路径数组**，而非普通字符串或JSON类型。你之前拼接的'{library,media}'是字符串格式，不符合操作符的参数要求。
• 正确实现：直接构建text[]类型的路径数组，无需拼接成类JSON字符串。可以通过array['library', subfolder]生成合法路径：

select
    e.id,
    e.name,
    rights #> array['library', subfolder]::text[] as allow
from
    emodels e,
    lateral (select 'media' as subfolder) subfolder

• 简化写法：如果子查询仅用于固定值，可以直接省略，简化为：

select
    id,
    name,
    rights #> array['library', 'media']::text[] as allow
from emodels

• 函数替代方案：也可以使用json_extract_path函数实现相同效果，参数直接传入路径节点即可：

select
    e.id,
    e.name,
    json_extract_path(rights, 'library', subfolder) as allow
from
    emodels e,
    lateral (select 'media' as subfolder) subfolder

内容的提问来源于stack exchange，提问作者vbulash