代码之家 › 专栏 › 技术社区 › Ken Chan

从EAV结构结果集中选择JSON值

entity-attribute-value jsonb postgresql

0

Ken Chan · 技术社区 · 6 年前

给出EAV结构中的结果集,如:

id   | attributeName  | stringValue | intValue  | BooleanValue
---------------------------------------------------------------
1       stringFoo            v1
1       stringFooList        v2   
1       stringFooList        v3
1       intFoo                           10
1       intFooList                       10
1       intFooList                       20
1       booleanFoo                                     true
1       booleanFooList                                 true 
1       booleanFooList                                 true

如何以json/jsonb格式选择所有属性和值对作为单个值,这类似于:

{
    "stringFoo"         : "v1" , 
    "stringFooList"     : ["v2","v3"] ,
    "intFoo"            : 10 ,
    "intFooList"        : [10,20],
    "booleanFoo"        : true,
    "booleanFooList"    : [true,true]
}

如果一个属性有多个属性值,例如 stringFooList ,它将把它格式化为JSON数组。

我正在使用PostgreSQL 9.6

2 回复 | 直到 6 年前

1

a_horse_with_no_name 6 年前

你可以这样做:

select id, jsonb_object_agg(att, value)
from (
  select id, 
         attributename as att, 
         case 
           when count(*) > 1 then 
               jsonb_agg(coalesce(stringvalue,intvalue::text,booleanvalue::text)) 
           else 
              to_jsonb(min(coalesce(stringvalue,intvalue::text,booleanvalue::text)))
         end as value
  from eav
  group by id, attributename
) t
group by id;

内部select将多个值聚合到一个JSON数组中,单个值聚合到JSON标量值中。然后外部查询构建所有行的单个JSON值。

在线示例: https://rextester.com/TLCRN79815

2

0

Ken Chan 6 年前

@a_horse_with_no_name 他的回答给了我一个良好的开端。我扩展了他/她的答案,并提出了以下查询,这样JSON数组中的元素具有与PostgreSQL中定义的相同的数据类型。

select id, jsonb_object_agg(att, 
    case 
      when strval is not null then strval
      when intvalue is not null then intvalue
      else boolVal
     end 
    )
from (
  select id, 
         attributename as att,  
         case when count(*) > 1 then 
            jsonb_agg(stringvalue) filter (where stringvalue is not null) 
         else 
            to_jsonb(min(stringvalue) filter (where stringvalue is not null))     
         end as strVal, 

         case when count(*) > 1 then 
            jsonb_agg(intvalue) filter (where intvalue is not null) 
         else 
            to_jsonb(min(intvalue) filter (where intvalue is not null))     
         end as intvalue, 

         case when count(*) > 1 then 
            jsonb_agg(booleanvalue) filter (where booleanvalue is not null) 
         else 
            to_jsonb(bool_and(booleanvalue) filter (where booleanvalue is not null))     
         end as boolVal
  from eav
  group by id, attributename
) t
group by id;