Requête jsonb imbriquée PostgresSQL pour plusieurs paires clé/valeur

Question

Je débute avec le type de données JSONB et j'espère que quelqu'un pourra m'aider.

J'ai un tableau ( propriétés ) avec deux colonnes ( id comme clé primaire et données comme jsonb). Les données est la structure :

    {
        "ProductType": "ABC",
        "ProductName": "XYZ",
        "attributes": [
            {
            "name": "Color",
            "type": "STRING",
            "value": "Silver"
            },
            {
            "name": "Case",
            "type": "STRING",
            "value": "Shells"
            },
            ...
        ]
    }

J'aimerais obtenir toutes les lignes où un attribut a une valeur spécifique, c'est-à-dire renvoyer toutes les lignes où Case = 'Shells' et/ou Couleur = "Rouge".

J'ai essayé ce qui suit, mais je ne parviens pas à appliquer deux conditions telles que Case = 'Shells' et Color = 'Silver'. Je peux obtenir des lignes lorsque le nom et la valeur d'un seul attribut correspondent aux conditions, mais je n'arrive pas à comprendre comment faire fonctionner cela pour plusieurs attributs.

---

EDIT 1 : Je parviens à obtenir les résultats en utilisant la requête suivante :

WITH properties AS (
    select *
    from (
        values 
        (1, '{"ProductType": "ABC","ProductName": "XYZ","attributes": [{"name": "Color","type": "STRING","value": "Silver"},{"name": "Case","type": "STRING","value": "Shells"}]}'::jsonb), 
        (2, '{"ProductType": "ABC","ProductName": "XYZ","attributes": [{"name": "Color","type": "STRING","value": "Red"},{"name": "Case","type": "STRING","value": "Shells"}]}'::jsonb)
    ) s(id, data)
)
select 
    *
from (
    SELECT 
        id,
        jsonb_object_agg(attr ->> 'name', attr -> 'value') as aggr
    FROM properties m,
       jsonb_array_elements(data -> 'attributes') as attr
    GROUP BY id
    ) a
where aggr ->> 'Color' = 'Red' and aggr ->> 'Case' LIKE 'Sh%'

Je pourrais potentiellement avoir des millions de ces enregistrements, donc je suppose que ma seule préoccupation est de savoir si c'est efficace et, si ce n'est pas le cas, s'il y a une meilleure façon de procéder.

Answer 1

démo pas à pas : db<>fiddle

SELECT 
    id
FROM properties m,
   jsonb_array_elements(data -> 'attributes') as attr
GROUP BY id
HAVING jsonb_object_agg(attr ->> 'name', attr -> 'value') @> '{"Color":"Silver", "Case":"Shells"}'::jsonb

Le problème est que jsonb_array_elements() déplace les deux valeurs liées dans des enregistrements différents. Cependant, cet appel est nécessaire pour récupérer les valeurs. Vous devez donc réagréger les valeurs après les avoir lues. Cela permettra de les vérifier de manière connexe.

Ceci peut être réalisé en utilisant la fonction jsonb_object_agg() la fonction d'agrégation. L'astuce consiste à créer un objet avec des attributs tels que "name":"value" . Ainsi, nous pouvons facilement vérifier si tous les attributs requis se trouvent dans l'objet JSON à l'aide de la fonction @> de l'opérateur.

---

En ce qui concerne l'"édition 1"

demo:db<>fiddle

Vous pouvez le faire :

SELECT
    *
FROM ( 
    SELECT 
        id,
        jsonb_object_agg(attr ->> 'name', attr -> 'value') as obj
    FROM properties m,
       jsonb_array_elements(data -> 'attributes') as attr
    GROUP BY id
) s
WHERE obj ->> 'Color' = 'Silver'
    AND obj ->> 'Case' LIKE 'Sh%'

1. Créer la nouvelle structure JSON comme décrit ci-dessus pour tous les JSON.
2. Filtrez ensuite ce résultat.

Vous pouvez également utiliser jsonb_object_agg() dans le HAVING aussi souvent que nécessaire. Je pense que vous devez vérifier quelle méthode est la plus performante dans votre cas :

SELECT 
    id
FROM properties m,
   jsonb_array_elements(data -> 'attributes') as attr
GROUP BY id
HAVING 
   jsonb_object_agg(attr ->> 'name', attr -> 'value') ->> 'Color' = 'Silver'
   AND
   jsonb_object_agg(attr ->> 'name', attr -> 'value') ->> 'Case' LIKE 'Sh%'