L'application de array_agg / array_to_json à une requête de modification de colonnes

Je suis en utilisant array_to_json en combinaison avec array_agg de format de certains résultats dans PostgreSQL en tant que JSON. Cela fonctionne très bien pour les requêtes dans lesquelles je veux retourner à la valeur par défaut d'une requête (toutes les colonnes, non modifiée). Mais je suis perplexe sur la façon dont je pourrais utiliser array_agg pour créer un objet JSON pour une requête dans laquelle je veux modifier une partie de la sortie.

Voici un exemple. Disons que nous avons les éléments suivants:

CREATE TABLE temp_user ( 
   user_id  serial PRIMARY KEY,
   real_name text
);
CREATE TABLE temp_user_ip (
   user_id  integer,
   ip_address text
);
INSERT INTO temp_user (user_id, real_name) VALUES (1, 'Elise'),  (2, 'John'), (3, NULL);
INSERT INTO temp_user_ip (user_id, ip_address) VALUES (1, '10.0.0.4'),  (2, '10.0.0.7'), (3, '10.0.0.9');

La requête suivante fonctionne:

# SELECT array_to_json(array_agg(temp_user)) as users from temp_user;
                                            users                                                
-----------------------------------------------------------------------------------------------------
 [{"user_id":1,"real_name":"Elise"},{"user_id":2,"real_name":"John"},{"user_id":3,"real_name":null}]

Mais disons que je n'aime pas la valeur null apparaissant pour l'utilisateur 3. Je préfère voir la chaîne "Utilisateur connecté à partir de $ip" à la place.

Je peux le faire:

# SELECT user_id, (CASE WHEN real_name IS NULL THEN (select 'User logged in from ' || ip_address FROM temp_user_ip WHERE user_id = temp_user.user_id) ELSE real_name END) as name from temp_user;

Et j'obtiens les résultats suivants:

 user_id |             name             
---------+------------------------------
       1 | Elise
       2 | John
       3 | User logged in from 10.0.0.9

Qui est grand. Mais je ne peux pas comprendre comment manipuler ces données dans un format JSON comme le premier exemple.

La sortie désirée est bien sûr la suivante:

[{"user_id":1,"name":"Elise"},{"user_id":2,"name":"John"},{"user_id":3,"name":"User logged in from 10.0.0.9"}]

Le suivant ne fonctionne pas:

# select array_to_json(array_agg ( (SELECT user_id, (CASE WHEN real_name IS NULL THEN (select 'User logged in from ' || ip_address FROM temp_user_ip WHERE user_id = temp_user.user_id) ELSE real_name END) as name from temp_user)));
ERROR:  subquery must return only one column

Je ne peux pas comprendre tout de façon à obtenir les données dans un format qui array_agg accepte. J'ai même essayé de créer un type personnalisé qui s'adapte le format de temp_user et d'essayer de array_agg appelle le constructeur de type, qui retourne la même erreur. L'erreur n'a pas de sens pour moi - si la sous-requête est agrégée, alors il ne devrait pas s'il revient plus d'une colonne. Tous les conseils?

Au lieu de le CAS, vous pouvez simplement utiliser FUSIONNENT(exp, exp, n) fusionnent pour retourner le premier pas de valeur nulle à partir de la liste d'arguments. E. g.: FUSIONNER(real_name, 'utilisateur connecté à partir de xxxx')

InformationsquelleAutor hs0 | 2012-12-11

json postgresql

Vous pouvez séparer les agrégats appel de la sous-requête et de l'utilisation de la ligne constructeur pour générer le composé de données:

SELECT 
    array_to_json(array_agg(row(t.*))) AS users 
FROM 
    (
        SELECT user_id, 
            CASE 
                WHEN real_name IS NULL 
                THEN (
                    SELECT 'User logged in from ' || ip_address 
                    FROM temp_user_ip 
                    WHERE user_id = temp_user.user_id
                ) ELSE real_name 
            END AS name 
        FROM temp_user
    ) t
;

Vous pouvez également le vérifier sur SQLFiddle.

Je confirme, cela fonctionne; j'ai juste appliqué cette méthode avec succès pour mon réel de requête. Merci!
La requête peut être simplifié un peu en remplaçant array_agg(row(t.*)) simplement array_agg(t). De cette façon, vous conservez aussi les noms de colonne de la sous-requête.

InformationsquelleAutor dezso

Juste une mise à jour ici, postgres dispose maintenant d'une fonction json_agg qui peuvent être utilisés dans leiu de array_to_json(array_agg( ... )) mais en fait, se comporte mieux dans certains cas, voir ici: Array_agg dans postgres sélectivement citations

Docs: https://www.postgresql.org/docs/9.5/static/functions-aggregate.html

voici la requête modifiée:

SELECT 
    json_agg(row(t.*)) AS users 
FROM 
    (
        SELECT user_id, 
            CASE 
                WHEN real_name IS NULL 
                THEN (
                    SELECT 'User logged in from ' || ip_address 
                    FROM temp_user_ip 
                    WHERE user_id = temp_user.user_id
                ) ELSE real_name 
            END AS name 
        FROM temp_user
    ) t
;

wow json_agg est super puissant!

InformationsquelleAutor Frank Conry

Vous devez vous connecter pour publier un commentaire.