convertir tableau JSON en tableau PostgreSQL pour être utilisé dans une jointure

voix
0

J'utilise Postgres 9.6, et j'ai les deux tableaux suivants:

create table employees (
  id text,
  setting_id text -- references settings.id
);

create table settings (
  id text,
  setting_str text -- contains json string
);

insert into employees (id, setting_id) values ('e1', 's1');
insert into employees (id, setting_id) values ('e2', 's2');

insert into settings (id, setting_str)
   values ('s1', '{vehicles : null}');
insert into settings (id, setting_str)
   values ('s2', '{vehicles : [Car, Bike]}');

Maintenant, je veux obtenir une sortie comme:

employee_id, name, vehicles
e1, one, null
e2, two, {Car, Bike}

J'ai essayé avec la requête suivante:

select e.id,
       jsonb_array_elements_text(s.setting_str::jsonb #> '{vehicles}')
from employees e
   join settings s on s.id = e.setting_id;

Mais il me donne une erreur:

ERROR:  cannot extract elements from a scalar

Toute idée comment je peux extraire le tableau JSON à partir du champ de texte et l' afficher comme un tableau de chaînes Postgres (pas un jsontableau, pas text) dans une instruction select?

Créé 27/11/2018 à 15:17
source utilisateur
Dans d'autres langues...                            


1 réponses

voix
0

Le modèle de données ne sont pas bonnes, de sorte que la requête sera compliquée.

Vous devez utiliser un modèle relationnel pour les données comme celui-ci.

Voici la requête avec commentaires en ligne:

SELECT e.id AS employee_id,
       s.id AS setting_id,
       /* construct an array of all vehicles that are not NULL */
       array_agg(v.p) FILTER (WHERE v.p IS NOT NULL) AS vehicles
FROM employees AS e
   JOIN settings AS s ON e.setting_id = s.id
   /* join with the "exploded" JSON array from s.setting_str */
   LEFT JOIN LATERAL json_array_elements_text(
                        /* replace "null" with an empty array */
                        COALESCE(
                           (s.setting_str::json) ->> 'vehicles',
                           '[]'
                        )::json
                     ) AS v(p) ON TRUE
GROUP BY e.id, s.id;

 employee_id | setting_id |  vehicles  
-------------+------------+------------
 e1          | s1         | 
 e2          | s2         | {Car,Bike}
(2 rows)
Créé 27/11/2018 à 16:24
source utilisateur

Cookies help us deliver our services. By using our services, you agree to our use of cookies. Learn more