如何在PostgreSQL中正确创建JSON?

huangapple go评论72阅读模式
英文:

How i can make json correctly in postgresql

问题

我有两个表,第一个表中有键,第二个表中有值。当我尝试生成 JSON 时,我得到了一个 JSON 数组,但我只想得到一个 JSON 对象。我该如何实现呢?

我有的是:

表1
key
KEY
表2
value
VALUE

我得到的是:

[
    {"key" : "value"}, 
    {"KEY" : "VALUE"}
]

我想要得到的是:

{
    "key" : "value",
    "KEY" : "VALUE"
}

查询语句:

SELECT jsonb_build_object(Table1.key, Table2.value)
FROM TABLE1
JOIN TABLE2 USING(guid)

英文:

I have two tables, in the first first I have keys, in second I have values. When i try to make JSON, I get array of JSON, but I want get only one ONE. How i can make it?

What I have:

Table 1
key
KEY
Table 2
value
VALUE

What I got:

[
    {"key" : "value"}, 
    {"KEY" : "VALUE"}
]

What I want to got:

{
    "key" : "value",
    "KEY" : "VALUE"
}

Query:

<code>SELECT jsonb_build_object(Table1.key, Table2.value)
FROM TABLE1
JOIN TABLE2 USING(guid)
</code>

答案1

得分: 1

你需要聚合JSONB对象。

可以尝试以下方法:

SELECT jsonb_object_agg(Table1.key, Table2.value)
FROM Table1
JOIN Table2 USING(guid)
英文:

You have to aggregate the JSONB objects.

Try something like this:

SELECT jsonb_object_agg(Table1.key, Table2.value)
FROM Table1
JOIN Table2 USING(guid)

huangapple
  • 本文由 发表于 2023年7月27日 17:31:37
  • 转载请务必保留本文链接:https://go.coder-hub.com/76778351.html
匿名

发表评论

匿名网友

:?: :razz: :sad: :evil: :!: :smile: :oops: :grin: :eek: :shock: :???: :cool: :lol: :mad: :twisted: :roll: :wink: :idea: :arrow: :neutral: :cry: :mrgreen:

确定