postgresql 从JSON中检索“SkipEndUserAcceptance”的值

8zzbczxx 于 2023-06-29 发布在 PostgreSQL

关注(0)|答案(2)|浏览(126)

我试图从JSON中检索信息，但信息的具体位置可能会有所不同。下面是我所拥有的数据的一个例子：
| 模板值| templatevalues |
| - -----| ------------ |
| {“complexTypeProperties”：[{“properties”：{“Value”：“NoDisruption”，“AttributeName”：“Urgency”}}}，{“properties”：{“Value”：“SingleUser”，“AttributeName”：“ImpactScope”}}，{“properties”：{“Value”：“448928”，“AttributeName”：“RegisteredForActualService”}}，{“properties”：{“Value”：“10146”，“AttributeName”：“Category”}}，{“properties”：{“Value”：“true，“AttributeName”：“SkipEndUserAcceptance”}}]}| {"complexTypeProperties":[{"properties":{"Value":"NoDisruption","AttributeName":"Urgency"}},{"properties":{"Value":"SingleUser","AttributeName":"ImpactScope"}},{"properties":{"Value":"448928","AttributeName":"RegisteredForActualService"}},{"properties":{"Value":"10146","AttributeName":"Category"}},{"properties":{"Value":true,"AttributeName":"SkipEndUserAcceptance"}}]} |
| {“complexTypeProperties”：[{“properties”：{“AttributeName”：“RegisteredForActualService”}}}，{“properties”：{“Value”：“SingleUser”，“AttributeName”：“ImpactScope”}}，{“properties”：{“Value”：“NoDisruption”，“AttributeName”：“Urgency”}}，{“properties”：{“Value”：“10154”，“AttributeName”：“Category”}}，{“properties”：{“Value”：“true”，“AttributeName”：“SkipEndUserAcceptance”}}]}| {"complexTypeProperties":[{"properties":{"AttributeName":"RegisteredForActualService"}},{"properties":{"Value":"SingleUser","AttributeName":"ImpactScope"}},{"properties":{"Value":"NoDisruption","AttributeName":"Urgency"}},{"properties":{"Value":"10154","AttributeName":"Category"}},{"properties":{"Value":"true","AttributeName":"SkipEndUserAcceptance"}}]} |
| {“complexTypeProperties”：[{“properties”：{“Value”：“SingleUser”，“AttributeName”：“ImpactScope”}}，{“properties”：{“Value”：“NoDisruption”，“AttributeName”：“Urgency”}}，{“properties”：{“Value”：“721846”，“AttributeName”：“RegisteredForActualService”}}，{“properties”：{“Value”：“10146”，“AttributeName”：“Category”}}，{“properties”：{“Value”：“true”，“AttributeName”：“SkipEndUserAcceptance”}}]}| {"complexTypeProperties":[{"properties":{"Value":"SingleUser","AttributeName":"ImpactScope"}},{"properties":{"Value":"NoDisruption","AttributeName":"Urgency"}},{"properties":{"Value":"721846","AttributeName":"RegisteredForActualService"}},{"properties":{"Value":"10146","AttributeName":"Category"}},{"properties":{"Value":"true","AttributeName":"SkipEndUserAcceptance"}}]} |
| {“complexTypeProperties”：[{“properties”：{“Value”：“SingleUser”，“AttributeName”：“ImpactScope”}}，{“properties”：{“Value”：“SlightDisruption”，“AttributeName”：“Urgency”}}，{“properties”：{“Value”：“2854102”，“AttributeName”：“RegisteredForActualService”}}，{“properties”：{“Value”：“10153”，“AttributeName”：“Category”}}，{“properties”：{“Value”：“435331”，“AttributeName”：“ServiceDeskGroup”}}]}| {"complexTypeProperties":[{"properties":{"Value":"SingleUser","AttributeName":"ImpactScope"}},{"properties":{"Value":"SlightDisruption","AttributeName":"Urgency"}},{"properties":{"Value":"2854102","AttributeName":"RegisteredForActualService"}},{"properties":{"Value":"10153","AttributeName":"Category"}},{"properties":{"Value":"435331","AttributeName":"ServiceDeskGroup"}}]} |
| {“complexTypeProperties”：[{“properties”：{“Value”：“NoDisruption”，“AttributeName”：“Urgency”}，{“properties”：{“Value”：“SingleUser”，“AttributeName”：“ImpactScope”}}，{“properties”：{“Value”：“10146”，“AttributeName”：“Category”}}，{“properties”：{“Value”：“597224”，“AttributeName”：“RegisteredForActualService”}}，{“properties”：{“Value”：true，“AttributeName”：“SkipEndUserAcceptance”}}]}| {"complexTypeProperties":[{"properties":{"Value":"NoDisruption","AttributeName":"Urgency"}},{"properties":{"Value":"SingleUser","AttributeName":"ImpactScope"}},{"properties":{"Value":"10146","AttributeName":"Category"}},{"properties":{"Value":"597224","AttributeName":"RegisteredForActualService"}},{"properties":{"Value":true,"AttributeName":"SkipEndUserAcceptance"}}]} |
| {“complexTypeProperties”：[{“properties”：{“Value”：“NoDisruption”，“AttributeName”：“Urgency”}，{“properties”：{“Value”：“SingleUser”，“AttributeName”：“ImpactScope”}}，{“properties”：{“Value”：“10146”，“AttributeName”：“Category”}}，{“properties”：{“Value”：“597224”，“AttributeName”：“RegisteredForActualService”}}，{“properties”：{“Value”：false，“AttributeName”：“SkipEndUserAcceptance”}}]}| {"complexTypeProperties":[{"properties":{"Value":"NoDisruption","AttributeName":"Urgency"}},{"properties":{"Value":"SingleUser","AttributeName":"ImpactScope"}},{"properties":{"Value":"10146","AttributeName":"Category"}},{"properties":{"Value":"597224","AttributeName":"RegisteredForActualService"}},{"properties":{"Value":false,"AttributeName":"SkipEndUserAcceptance"}}]} |
我需要从JSON中检索“SkipEndUserAcceptance”的值。此值可以是“false”或“true”。目前，我使用的表达式是：

TEMPLATEVALUES::jsonb -> 'complexTypeProperties' @> '[{"properties":{"AttributeName":"SkipEndUserAcceptance","Value":"true"}}]' AS "SkipEndUserAcceptance"

去取回它但是，返回的值似乎是不可预测的。
您能否建议一个检索“SkipEndUserAcceptance”值的解决方案？此外，如果属性“SkipEndUserAcceptance”不存在，则应将其设置为“false”。
电流输出：
| 跳过EndUserAcceptance| SkipEndUserAcceptance |
| - -----| ------------ |
| 假的| false |
| 真的| true |
| 真的| true |
| 假的| false |
| 假的| false |
| 假的| false |
预期输出：
| 跳过EndUserAcceptance| SkipEndUserAcceptance |
| - -----| ------------ |
| 真的| true |
| 真的| true |
| 真的| true |
| 假的| false |
| 真的| true |
| 假的| false |
小提琴：https://www.db-fiddle.com/f/6AacERW43QoJ5Ggb3X4Dys/0

postgresql

来源：https://stackoverflow.com/questions/76529756/retrieving-the-value-of-skipenduseracceptance-from-a-json

2条答案

按热度按时间

56lgkhnf1#

使用此标量子查询

(
   select (j ->> 'Value')::boolean 
   from jsonb_path_query(templatevalues, '$.complexTypeProperties[*].properties') as j
   where j ->> 'AttributeName' = 'SkipEndUserAcceptance'
 )

提取SkipEndUserAcceptance值，并将coalesce值提取为false，以获得预期结果。这就是：

select id, 
 coalesce((
   select (j ->> 'Value')::boolean 
   from jsonb_path_query(templatevalues, '$.complexTypeProperties[*].properties') as j
   where j ->> 'AttributeName' = 'SkipEndUserAcceptance'
 ), false)  as "SkipEndUserAcceptance"
from the_table;

参见Demo。

赞(0）回复(0）举报 2023-06-29

sg24os4d2#

使用jsonb_to_recordset从数组创建一个表：

SELECT id
     , COALESCE((MAX(properties ->> 'Value')
                 FILTER (WHERE properties ->> 'AttributeName' = 'SkipEndUserAcceptance'))::BOOL
    , FALSE) AS skipenduseracceptance
FROM tbl_temp
   , JSONB_TO_RECORDSET(templatevalues -> 'complexTypeProperties') jtr(properties JSONB)
GROUP BY id
ORDER BY id;

赞(0）回复(0）举报 2023-06-29

我来回答

postgresql 从JSON中检索“SkipEndUserAcceptance”的值

2条答案

相关问题

热门标签

最新问答