Postgres Consultar/filtragem de JSONB com matrizes aninhadas

0

Pergunta

Abaixo está o meu exemplo de requisito

Eu quero os clientes que atenderem a todas as condições abaixo

  1. No país "xyz", constituída entre 2019 a 2021.
  2. Deve ter pelo menos uma conta com o equilíbrio entre 10000 e 13000 e ramo é "abc" e datas das transacções entre 20200110 e 20210625. Ele é formatado e armazenado como um número
  3. Deve ter pelo menos um endereço no estado "state1" e códigos pin, entre 625001 e 625015

Abaixo está a estrutura de uma tabela

        CREATE TABLE IF NOT EXISTS customer_search_ms.customer
        (
            customer_id integer,
            customer_details jsonb
        )
    

Pode haver milhões de linhas na tabela. Eu criei GIN índice de tipo jsonb_ops no customer_details coluna como gostaríamos de ser, também, a verificação de condições de existência e comparação de intervalo

Abaixo está um exemplo de dados no customer_data JSONB coluna

customer_id : 1

    {
        "customer_data": {
            "name": "abc",
            "incorporated_year": 2020,
            "country":"xyz",
            "account_details": [
                {
                    "transaction_dates": [
                        20180125, 20190125, 20200125,20200525
                    ],
                    "account_id": 1016084,
                    "account_balance": 2000,
                    "account_branch": "xyz"
                },
                {
                    "transaction_dates": [
                        20180125, 20190125, 20200125
                    ],
                    "account_id": 1016087,
                    "account_balance": 12010,
                    "account_branch": "abc"
                }
            ],
            "address": [
                {
                    "address_id": 24739,
                    "door_no": 4686467,
                    "street_name":"street1",
                    "city": "city1",
                    "state": "state1",
                    "pin_code": 625001
                },
                {
                    "address_id": 24730,
                    "door_no": 4686442,
                    "street_name":"street2",
                    "city": "city1",
                    "state": "state1",
                    "pin_code": 625014
                }
            ]
        }
    }

Agora a consulta de eu ter escrito acima é

SELECT  c.customer_id,
        c.customer_details
FROM customer_search_ms.customer c
WHERE c.customer_details @@ CAST('$.customer_data.country ==  "xyz" && $.customer_data.incorporated_year >= 2019 && $.customer_data.incorporated_year <= 2021 ' AS JSONPATH)
AND c.customer_details @? CAST('$.customer_data.account_details[*] ? (@.account_balance >=  10000) ? (@.account_balance <=  13000) ?(@.account_branch ==  "abc") ? (@.transaction_dates >=  20200110) ? (@.transaction_dates <=  20210625)' AS JSONPATH)
AND c.customer_details @? CAST('$.customer_data.address[*] ? (@.state ==  "state1") ? (@.pin_code >=  625001) ? (@.pin_code <= 625015)  ' AS JSONPATH)

Para lidar com o cenário acima é a melhor maneira de escrever. É possível combinar todos os 3 critérios (customer/account/endereço) em uma expressão? A tabela irá ter milhões de linhas. Eu sou da opinião de tê-lo como uma expressão e bater o DB vai dar o melhor de desempenho. É possível combinar essas 3 condições como uma expressão

1

Melhor resposta

0

Sua consulta não me dá o erro de relatório. Em vez disso, ele é executado, mas não deixa de dar o "errado" resultados em relação ao que você deseja. Existem vários erros que não são erros de sintaxe, mas é só dar resultados errados.

Seu primeiro jsonpath parece bem. É uma expressão Booleana, e @@ verifica-se que a expressão rendimentos true.

Seu segundo jsonpath tem dois problemas. Ele retorna uma lista de objetos que correspondem às suas condições. Mas não são objetos booleanos, de modo @@ vai ser infeliz e retornar SQL NULL, que é tratada como false aqui. Em vez disso, você precisa testar se que a lista é vazia. Isto é o que @? faz, então, uso que, em vez de @@. Além disso, as datas são armazenadas como de 8 dígitos com números inteiros, mas você está comparando-as 8 caracteres, cadeias de caracteres. Em jsonpath, tais cruz-tipo de comparações de rendimento JSON nulo, que é tratada como false aqui. Assim você nem precisa alterar o armazenamento de seqüências de caracteres, ou alterar os literais eles são comparados em números inteiros.

O terceiro jsonpath também tem o @@ problema. E tem o reverso do tipo de problema, você tem a pin_code armazenadas como cadeias de caracteres, mas você testá-las contra números inteiros. Finalmente, você tem 'pin_code' com erros ortográficos em uma ocorrência.

2021-11-24 20:58:29

Graças Janes. Eu ter corrigido o código e os dados no post original. Devido à natureza confidencial que eu tinha para postar cozido de dados e fez o erro em que. Eu não sou capaz de reproduzir o cenário de erro. Existe alguma melhor aproximação para consulta dada acima com 3 condições na cláusula where. Meu pensamento é que se eu sou capaz de fazê-lo como uma condição única, em vez de 3 que vai ser melhor. Qualquer orientação será de grande ajuda para mim. Obrigado
Balaji Govindan

Em outros idiomas

Esta página está em outros idiomas

Русский
..................................................................................................................
Italiano
..................................................................................................................
Polski
..................................................................................................................
Română
..................................................................................................................
한국어
..................................................................................................................
हिन्दी
..................................................................................................................
Français
..................................................................................................................
Türk
..................................................................................................................
Česk
..................................................................................................................
ไทย
..................................................................................................................
中文
..................................................................................................................
Español
..................................................................................................................
Slovenský
..................................................................................................................