Perguntas[json](dba)

Em um banco de dados postgresql, tenho uma tabela com 2 colunas:

A coluna1 é do tipo VARCHAR e contém várias linhas de ids neste formato: ID1123312

A coluna2 é do tipo JSONB e contém várias linhas de jsons que possuem esta estrutura:

{
    "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"
        }
    ]
}

Eu preciso construir uma instrução SELECT para retornar apenas os valores da propriedade b2 de todas as linhas da Coluna2. Se tiver apenas uma linha, então apenas '1231342138adc2fehj3j21321321321kjk1423j32k9'. Se tiver várias linhas, qualquer valor que encontrar dentro de cada jsonb dentro de cada linha.

Qualquer ajuda é muito apreciada.

Eu tentei:

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

SELECT * retorna a linha inteira da tabela, mas não sei como instruí-la a retornar apenas qualquer valor que a propriedade b2 contenha (aninhada dentro do json da linha).

Em um banco de dados postgresql, tenho uma tabela com 2 colunas:

A coluna1 é do tipo VARCHAR e contém várias linhas de ids neste formato: ID1123312

A coluna2 é do tipo JSONB e contém várias linhas de jsons que possuem esta estrutura:

{
    "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"
        }
    ]
}

Eu preciso construir uma instrução SELECT para retornar a linha acima dando o valor de b2 (1231342138adc2fehj3j21321321321kjk1423j32k9) como parâmetro de entrada.

Algo como:

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

Ou apenas:

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

Só não sei como dar esta instrução.

Qualquer ajuda é muito apreciada.

Eu tenho uma consulta de contenção JSON como abaixo. A consulta procura objetos correspondentes dentro de matrizes. Isso funciona.

Os valores de Name e DataType serão passados ​​para uma função .Net, e a consulta será construída em NpgSql. Quero parametrizar os valores (cidade, string) para evitar injeção de SQL. Como conseguir isso?

De acordo com este problema do GitHub , tentei construir o json usando jsonb_build_object, mas preciso construir arrays de objetos. Me disseram que funções agregadas não podem ser usadas em WHERE, então isso não funciona.

Então, posso construir uma matriz de objetos para adicionar os valores dos parâmetros ou existe uma maneira melhor de evitar a injeção de SQL nesta consulta?

No momento, estamos usando o Postgres 10.18, então, idealmente, a solução deve funcionar nisso. No entanto, em breve atualizaremos para 14, portanto, soluções melhores para 14 também seriam de interesse.

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

Se eu tiver uma tabela com uma coluna contendo um documento JSON válido arbitrário, posso incorporar esse documento em uma consulta retornando JSON diferente de uma string?

Por exemplo:

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;

Saída real:

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

Saída desejada (observe que o valor analisado de Documentfoi incorporado):

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

Eu armazenei dados jsonb no PostgreSQL 13 assim:

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

como consultar o tipo de dados jsonb? Eu tentei assim:

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"}';

ambos não poderiam funcionar. esta é a tabela DDL:

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

o que devo fazer para que funcione? Também tentei assim:

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

Antes de tudo, gostaria de dizer que não tenho muita experiência, mas comecei a aprender dbs depois de ter algumas matérias relacionadas a dbs na minha universidade (então, lembre-se disso enquanto estiver lendo isso).

Eu já fiz essa pergunta no stackoverflow, mas não obtive uma resposta conclusiva, então pensei que seria uma boa ideia fazer a mesma pergunta aqui, pois este site é focado apenas em bancos de dados e seus prós e contras.

Desejo alterar um único elemento de uma matriz aninhada usando os comandos UPDATE, DELETE ou INSERT. Neste caso, eu queria atualizar um único elemento de um array trocando um dos valores 'lastName' por outro usando o comando UPDATE (a sintaxe que estou usando provavelmente está errada, mas isso é o máximo que consegui descobrir do que eu já li na documentação oficial). Eu usei o dbfiddle para testar se o que estou fazendo funcionaria antes de colocar qualquer coisa no banco de dados real para não cometer erros desnecessários. Aqui está o que escrevi em dbfiddle https://dbfiddle.uk/?rdbms=postgres_10&fiddle=ecc2329efea6af28636b3537d46b6c01

Agradeceria se alguém pudesse esclarecer o que estou fazendo de errado. Se você tiver alguma sugestão sobre como esse tipo de coisa deve ser feito, ficarei mais do que feliz em receber algum feedback construtivo.

Estou lutando para tentar agregar resultados em uma tabela em um json aninhado.

esta é a tabela:

+----+------+--------+------+------+-------+-----------+--------------+
| 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 |
+----+------+--------+------+------+-------+-----------+--------------+

Eu gostaria de criar uma view mostrando algo assim:

+--------+---------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| 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]}]}] |
+--------+---------------------------------------------------------------------------------------------------------------------------------------------------------------------+

agrupando todos os jogos, cada um com seus passos na área a que pertencem. Este é um dbfiddle que criei.

Eu tentei abordagens diferentes, como começar com

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 ;

que surpreendentemente não agrupa por área, ou

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 ;

ou muitas outras tentativas. Eu posso obter resultados de área discreta como:

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;
                

mas qualquer tentativa de agregar áreas em uma matriz de objetos falha. Alguém poderia me ajudar a undestad o que estou perdendo?

ATUALIZAÇÃO :

isso é algo mais próximo do que eu quero obter:

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

mas ainda não agrupa os jogos aninhando-os em superobjetos de área. continua lutando..

Eu quero atualizar meu raw com a condição if-else. Aqui está meu código 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

Minha mesa;

Após este código sql id= 3deve ser como id = 2.
Para resumir,: se json contiver, remova do json; se o json não contiver, envie os dados para o array

Eu tinha ouvido falar que no postgresql, o jsontipo preserva a ordenação declarada ao contrário de sua contraparte totalmente analisada jsonb.

Eu quero obter a posição ordinal de cada chave em um documento fornecido pelo cliente. Pelo que eu tentei , porém, isso não parece possível.

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

A consulta anterior me mostra o valor do nó folha 3 na posição ordinal 2, apesar de ser fornecido como a terceira chave, mas não parece haver um json_path_querycompanheiro para jsonb_path_query.

Existe uma função interna para analisar jsonchaves? Ou estou limitado a uma análise mais rigorosa jsonbou completamente irrestrita textpara extrair essas informações?

Eu tenho uma tabela com 3 colunas no Postgres 14.2: name, name_adds, aditional.

E essas regras:

1. namee name_addspode ser preenchido com o mesmo valor ou não. Exemplo: nome (john) e name_adds (doe) ou ambos o valor é (john)

2. coluna additionaltem um dos dois formatos:

   formato 1:

   {"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}]}

   formato 2:

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

3. Se (nome = name_adds) soma padrão -> valor_1

4. If (name != name_adds) soma non_default -> value_1

Como posso somar o formato 1 ou 2 do ponto 2. com base nas regras do ponto 3. e 4. ?

Exemplo de tabela:

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}]

Resultado esperado:

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

john     doe        600

downy    downy      11

downy    dan        11

Tentei uma consulta nesta demonstração online , mas não consegui obter o resultado esperado.