Postgres Consulta y filtrado de JSONB con matrices anidadas

0

Pregunta

A continuación es mi ejemplo de requisito

Quiero que los clientes que cumplan todas las condiciones siguientes.

  1. En el país "xyz", incorporado entre 2019 y 2021.
  2. Debe tener al menos una cuenta con el equilibrio entre 10000 y 13000 y rama es "abc" y las fechas de las transacciones entre 20200110 y 20210625. Es el formato y se almacenan como el número
  3. Debe tener al menos una dirección en el estado "estado1" y los códigos pin entre 625001 y 625015

A continuación se estructura de la tabla

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

Puede haber millones de filas en la tabla. He creado GIN índice de tipo de jsonb_ops en el customer_details columna como también estaríamos comprobando la existencia de las condiciones y el rango de comparación

A continuación se muestra un ejemplo de datos en el customer_data JSONB columna

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

Ahora la consulta que he escrito anteriormente es

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 manejar el escenario anterior es la mejor manera de escribir. Es posible combinar todos los 3 criterios (cliente/cuenta/dirección) en una sola expresión? La tabla tendrá millones de filas. Yo soy de la opinión como una de las expresiones y golpear la base de datos le dará el mejor rendimiento. Es posible combinar estas 3 condiciones como una expresión

1

Mejor respuesta

0

Su consulta no me da el error que informe. Más bien, se ejecuta, pero no dar el "mal" los resultados en comparación con lo que usted desea. Hay varios errores en ella que no son errores de sintaxis, pero acaba de dar resultados equivocados.

Su primer jsonpath se ve bien. Es una expresión Booleana, y @@ comprueba si que la expresión de los rendimientos true.

Su segundo jsonpath tiene dos problemas. El resultado es una lista de objetos que coincidan con sus condiciones. Pero los objetos no son booleanos, así @@ será infeliz y devolver NULL de SQL, el cual es tratado de la misma como falso aquí. En su lugar, usted necesita para poner a prueba si la lista está vacía. Esto es lo que @? no, por lo que en lugar de utilizar @@. También, las fechas se almacenan como de 8 dígitos enteros, pero estás comparando a 8 cadenas de caracteres. En jsonpath, tales cruz-tipo de comparaciones de rendimiento JSON null, el cual es tratado de la misma como falso aquí. Así que usted necesita para cambiar el almacenamiento de cadenas, o cambiar los literales que son de en comparación a en números enteros.

Su tercer jsonpath también tiene la @@ problema. Y tiene el inverso del tipo de problema, usted tiene la pin_code almacenan como cadenas, pero las pruebas en contra de los enteros. Por último se han 'pin_code' mal escrito en una aparición.

2021-11-24 20:58:29

Gracias Janes. He corregido el código y los datos en el post original. Debido a la naturaleza confidencial tuve que post cocido de datos y lo hizo de error en eso. Yo no soy capaz de reproducir el escenario de error. Es el mejor enfoque para la consulta dada de arriba con 3 condiciones en la cláusula where. Mi pensamiento es que si soy capaz de hacer como una sola condición, en lugar de 3 va a ser mejor. Cualquier orientación será de gran ayuda para mí. Gracias
Balaji Govindan

En otros idiomas

Esta página está en otros idiomas

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