问题[json](dba)

在 postgresql 数据库中，我有一个包含 2 列的表：

Column1 是 VARCHAR 类型，包含多行 id，格式如下：ID1123312

Column2 是 JSONB 类型，包含多行具有以下结构的 json：

{
    "a": [
        {
            "a1": "2022-01-01",
            "a2": "2026-01-31",
            "a3": 1
        }
    ],
    "b": [
        {
            "b1": "J",
            "b2": "1231342138adc2fehj3j21321321321kjk1423j32k9"
        }
    ],
    "c": [
        {
            "c1-1": "2021-02-01",
            "c1-2": "2021-01-01"
        },
        {
            "c2-1": "2021-04-01",
            "c2-2": "2021-03-01"
        }
    ]
}

我需要构建一个 SELECT 语句以仅从Column2 的所有行返回 b2 属性的值。如果它只有一行，那么只有'1231342138adc2fehj3j21321321321kjk1423j32k9'。如果它有多行，则它在每行内的每个 jsonb 中找到的任何值。

任何帮助是极大的赞赏。

我试过了：

SELECT *
FROM t
WHERE jsonb_path_exists(t.jsonColumn, '$.b[*].b2 ? (@ == "1231342138adc2fehj3j21321321321kjk1423j32k9")')

SELECT * 返回表的整行，但我不知道如何指示它只返回 b2 属性包含的任何值（嵌套在行中的 json 内）。

在 postgresql 数据库中，我有一个包含 2 列的表：

Column1 是 VARCHAR 类型，包含多行 id，格式如下：ID1123312

Column2 是 JSONB 类型，包含多行具有以下结构的 json：

{
    "a": [
        {
            "a1": "2022-01-01",
            "a2": "2026-01-31",
            "a3": 1
        }
    ],
    "b": [
        {
            "b1": "J",
            "b2": "1231342138adc2fehj3j21321321321kjk1423j32k9"
        }
    ],
    "c": [
        {
            "c1-1": "2021-02-01",
            "c1-2": "2021-01-01"
        },
        {
            "c2-1": "2021-04-01",
            "c2-2": "2021-03-01"
        }
    ]
}

我需要构建一个 SELECT 语句来返回上面的行，将 b2 (1231342138adc2fehj3j21321321321kjk1423j32k9) 的值作为输入参数。

就像是：

SELECT * 
FROM table 
WHERE [Column2 contains b which contains b2 which has value '1231342138adc2fehj3j21321321321kjk1423j32k9'] ; 

要不就：

SELECT * 
FROM table 
WHERE [value '1231342138adc2fehj3j21321321321kjk1423j32k9' exists in row from Column2 ]

我只是不知道如何给出这个指令。

任何帮助是极大的赞赏。

我有一个 JSON 包含查询，如下所示。该查询在数组中查找匹配的对象。这行得通。

Name 和 DataType 的值将被传递给一个 .Net 函数，并在 NpgSql 中构建查询。我想参数化值（城市，字符串）以避免 SQL 注入。如何做到这一点？

根据这个 GitHub 问题，我尝试使用 jsonb_build_object 构建 json，但我需要构建对象数组。我被告知不能在 WHERE 中使用聚合函数，所以这不起作用。

那么，我可以构建一个对象数组来添加参数值，还是有更好的方法来避免这个查询中的 SQL 注入？

我们目前正在使用 Postgres 10.18，因此理想情况下该解决方案应该可以使用。但是，我们将很快升级到 14，因此对于 14 的更好解决方案也将引起人们的兴趣。

SELECT name FROM (SELECT name, fields -> 'dimensions' as dimensions
from data) x
WHERE dimensions @> '[{"Name": "city"}, {"DataType": "string"}]'

如果我有一个包含任意有效 JSON 文档的列的表，我可以将该文档嵌入到返回 JSON 的查询中，而不是作为字符串？

例如：

CREATE TABLE #Example (Name nvarchar(50) not null, Document nvarchar(max) not null);

INSERT INTO #Example VALUES 
    ('Document 1', '{ "a": "a" }'),
    ('Document 2', '{ "b": "b" }');

SELECT *
FROM #Example
FOR JSON AUTO;

实际输出：

[
    {
        "Name":"Document 1",
        "Document":"{ \"a\": \"a\" }"
    },
    {
        "Name":"Document 2",
        "Document":"{ \"b\": \"b\" }"
    }
]

期望的输出（注意解析后的值Document已经嵌入）：

[
    {
        "Name":"Document 1",
        "Document": { "a": "a" }
    },
    {
        "Name":"Document 2",
        "Document": { "b": "b" }
    }
]

我在 PostgreSQL 13 中存储了 jsonb 数据，如下所示：

[{"code": "OFFICIAL"}, {"code": "FULLTEXT"}]

如何查询jsonb数据类型？我试过这样：

select count(*) from test where tags::jsonb ? 'code';
select count(*) from test where tags::jsonb ->> 'code' = 'OFFICIAL';
select count(*) from test where tags::jsonb @> '{"code": "OFFICIAL"}';

两者都无法工作。这是表 DDL：

CREATE TABLE public.test (
    id int8 NOT NULL GENERATED ALWAYS AS IDENTITY,
    tags jsonb NULL,
    CONSTRAINT test_pkey PRIMARY KEY (id)
);

我应该怎么做才能让它工作？也试过这样：

select * from (
    select jsonb_array_elements(tags) as tt from test
) a
where  tt -> 'code' = 'OFFICIAL'

首先我想说我没有太多经验，但是我在大学学习了一些与 dbs 相关的科目后开始学习 dbs（所以在阅读本文时请记住这一点）。

我已经在 stackoverflow 上问过这个问题，但我没有得到结论性的答案，所以我认为在这里问同样的问题可能是个好主意，因为这个网站只关注数据库及其来龙去脉。

我想使用 UPDATE、DELETE 或 INSERT 命令更改嵌套数组的单个元素。在这种情况下，我想通过使用 UPDATE 命令将一个“lastName”值换成另一个来更新数组的单个元素（我使用的语法可能是错误的，但这是我能从我的'已阅读官方文档）。在将任何内容放入真实数据库之前，我使用 dbfiddle 测试我正在做的事情是否可行，以免犯任何不必要的错误。这是我在 dbfiddle https://dbfiddle.uk/?rdbms=postgres_10&fiddle=ecc2329efea6af28636b3537d46b6c01中写的

如果有人能澄清我做错了什么，我将不胜感激。如果您对如何完成此类事情有任何建议，我将非常乐意获得一些建设性的反馈。

我正在努力尝试将表中的结果聚合到嵌套的 json 中。

这是表格：

+----+------+--------+------+------+-------+-----------+--------------+
| id | area | userId | game | step | score | completed | validAnswers |
+----+------+--------+------+------+-------+-----------+--------------+
|  1 |    2 |     21 |    7 |   53 |    10 |         0 |            0 |
|  2 |    2 |     37 |    7 |   53 |     0 |         0 |            0 |
|  3 |    2 |     21 |    7 |   53 |    10 |         0 |            0 |
|  4 |    2 |     37 |    7 |   53 |    10 |         0 |            0 |
...
| 37 |    3 |     21 |    7 |   57 |    80 |         1 |            8 |
| 38 |    2 |     21 |    8 |   56 |    80 |         1 |            8 |
| 39 |    2 |     21 |    7 |   58 |   100 |         1 |           10 |
| 40 |    2 |     21 |    7 |   59 |    50 |         1 |            5 |
+----+------+--------+------+------+-------+-----------+--------------+

我想创建一个显示如下内容的视图：

+--------+---------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| userId | completedSteps                                                                                                                                                      |
+--------+---------------------------------------------------------------------------------------------------------------------------------------------------------------------+
|     21 | [{"area": 2, "games": [{"id": 7, "steps": [58, 59]},{"id":8,"steps":[15,16,17]}]},{"area": 3, "games": [{"id": 1, "steps": [34, 18]},{"id":4,"steps":[11,12,14]}]}] |
|     18 | [{"area": 2, "games": [{"id": 7, "steps": [58, 59]},{"id":8,"steps":[15,16,17]}]},{"area": 3, "games": [{"id": 1, "steps": [34, 18]},{"id":4,"steps":[11,12,14]}]}] |
|     23 | [{"area": 2, "games": [{"id": 7, "steps": [58, 59]},{"id":8,"steps":[15,16,17]}]},{"area": 3, "games": [{"id": 1, "steps": [34, 18]},{"id":4,"steps":[11,12,14]}]}] |
|     11 | [{"area": 2, "games": [{"id": 7, "steps": [58, 59]},{"id":8,"steps":[15,16,17]}]},{"area": 3, "games": [{"id": 1, "steps": [34, 18]},{"id":4,"steps":[11,12,14]}]}] |
+--------+---------------------------------------------------------------------------------------------------------------------------------------------------------------------+

将所有游戏及其步骤分组到它们所属的区域中。 这是我创建的 dbfiddle。

我尝试了不同的方法，比如从

select userId,     
       json_arrayagg(
                select distinct area
                from scoreTable st2
                where st1.userId =st2.userId and
                st1.area=st2.area
                group by area
        ) as completedSteps
from scoreTable st1
where completed = 1
group by userId ;

奇怪的是，它没有按区域分组，或者

select userId,
       json_objectagg('areas',
                      (select distinct area
                       from scoreTable st2 
                       where st1.userId =st2.userId
                       group by area)) as completedSteps
from scoreTable st1
where completed = 1
group by userId ;

或许多其他尝试。我可以获得离散区域结果，例如：

select area,
       json_object('games',json_object('id',game,'steps',JSON_ARRAYAGG(step))) as completedSteps
from scoreTable
where completed = 1
group by userId,area,game;
                

但是任何试图将区域聚合成对象数组的尝试都失败了。任何人都可以帮助我了解我错过了什么吗？

更新：

这更接近我想要得到的东西：

select userId, json_arrayagg(json_object('completed',completed)) as completed
from (
select distinct userId, json_arrayagg(json_object('area',area,'games',completed)) as completed
from (
    select distinct userId,area, json_object('id',game,'steps',(json_arrayagg(step))) as completed
from scoreTable
where completed = 1  and userId = 21
group by area,game
) st1
group by userId, area
) st3
group by userId

但仍然没有将嵌套它们的游戏分组到区域超级对象中。继续挣扎。。

我想用 if-else 条件更新我的原始数据。这是我的sql代码；

update posts
    set like = 
      if(json_contains(like,2,'$'),
          json_remove(like,replace(json_search(like,'one',2),'"','')),
          json_array_append(like,'$',2)
        )
     where id = 3

我的桌子；

这段 sql 代码之后id= 3应该是这样的id = 2。
综上，：如果json包含，则从json中移除；如果 json 不包含，则将数据推送到数组

我听说 在postgresql 中，与完全解析的对应类型不同，该json类型保留了声明的顺序jsonb。

我想获取客户端提供的文档中每个键的序号位置。从我尝试过的情况来看，这似乎是不可能的。

select *
from jsonb_path_query(
    '[{"foo":1},{"foo":2,"bar":3}]'::jsonb,
    '$.*'::jsonpath
) with ordinality

前面的查询向我显示了位于序号位置 2 的叶节点值 3，尽管它是作为第三个键提供的，但似乎json_path_query没有jsonb_path_query.

json是否有解析键的内置函数？或者我是否仅限于更严格jsonb或完全不受约束text的解析来提取这些信息？

我在 Postgres 14.2 中有一个包含 3 列的表：name, name_adds, aditional.

这些规则：

1. name并且name_adds可以填充相同的值或不填充。示例：name (john) 和 name_adds (doe) 或两者的值为 (john)

2. 列additional具有以下两种格式之一：

   格式一：

   {"default":[{"value_1": 100, "value_2": 0.1},{"value_1": 200, "value_2": 0.2}], "non_default":[{"value_1": 200, "value_2": 0.1}, {"value_1": 400, "value_2": 0.1}]}

   格式 2：

   [{"value_1": 10,"value_2": 11},{"value_1": 1,"value_2": 19}

3. if (name = name_adds) sum default -> value_1

4. if (name != name_adds) sum non_default -> value_1

如何根据第3点和第 4点中的规则对第2点的格式 1 或 2 求和？

示例表：

name    name_adds   additional
---------------------------------------------------------------
john    john      {"default":[{"value_1": 100, "value_2": 0.1}, 
                  {"value_1": 200, "value_2": 0.2}],"non_default": 
                  [{"value_1": 200, "value_2": 0.1}, {"value_1": 400, 
                   "value_2": 0.1}]}

john     doe     {"default":[{"value_1": 100, "value_2": 0.1}, 
                  {"value_1": 200, "value_2": 0.2}],"non_default": 
                  [{"value_1": 200, "value_2": 0.1}, {"value_1": 400, 
                   "value_2": 0.1}]}

downy    downy    [{"value_1": 10, "value_2": 11},{"value_1": 1,"value_2": 19}]

downy    dan      [{"value_1": 10, "value_2": 11},{"value_1": 1,"value_2": 19}]

预期结果：

name    name_adds   sum_result
---------------------------------------------------------------
john    john        300

john     doe        600

downy    downy      11

downy    dan        11

我在这个在线演示中尝试了一个查询，但没有得到预期的结果。