[Python] Postgresql json columns was: Psycopg2 e serializzazione delle SELECT in JSON

[Daniele Varrazzo]

On 2014-10-16 16:38, Carlos Catucci wrote:
> Approfitto del thread su psycopg2 per formulare una domnada dborder 
> line
> all'OT.
> 
> Sto cercando ddi usre delle colonne JSON con Postgres 9.3/Django 1.6+.
> Ovvio che devo usare (*) delle rwa query. Solo che fatico a ottenere un
> risultato con una struttura di questo tipo:
> 
> data = '{ "A": { "B": [ { "C": 1, "D": 2 }, { "C": 3, "D": 4 }, { 
> "C":1,
> "D": 6, "E": 3 } ] } }'
> 
> per fare una query dove mi deve tornare tutti i record, per dire che
> abbianno
> 
> tabella.data->'A'->'B'->'C' == 1
> 
> ho provato a usare strutture funzioni built-in di Postgres ma senza
> riuscire a cavare il ragno dal buco. Nella migliore delle query ottengo 
> un
> resultset vuoto, nelle altre (quasi sempre) dei simpaticissimi errori.
> 
> Qualcuno ha avuto problemi similari o una qualche idea su dove posso
> trovare un esempio con una query tipo questa? Premetto che se invece 
> che
> una array di hash (dei dict in python)  avessi un array di valori (una
> lista insomma) avrei la soluzione ma cosi' ci sto dando di matto.

Non ci ho ancora giocato troppo con le funzioni json di postgres. 
Guardando i doc, json_populate_recordset sembra promettente: converte 
una lista json in un recordset. Devi definire un tipo che contiene i 
dati che ti servono (e' sufficiente che contenga il campo su cui devi 
filtrare, gli altri vengono scaricati):

     piro=# create type tmp as ("C" int, "D" int);
     CREATE TYPE

     piro=# select * from json_populate_recordset(null::tmp,
         '{ "A": { "B": [ { "C": 1, "D": 2 }, { "C": 3, "D": 4 }, { 
"C":1, "D": 6, "E": 3 } ] } }'::json -> 'A' -> 'B');
      C | D
     ---+---
      1 | 2
      3 | 4
      1 | 6
     (3 rows)

A questo punto "e' facile" tirare un paio di sottoquery ed ottenere solo 
i record per cui esiste un valore di questo "srotolato" per cui C = 1.

     piro=# create table mydata(id serial primary key, data json);
     CREATE TABLE
     piro=# insert into mydata (data) values ('{ "A": { "B": [ { "C": 1, 
"D": 2 }, { "C": 3, "D": 4 }, { "C":1, "D": 6, "E": 3 } ] } }');
     INSERT 0 1
     piro=# insert into mydata (data) values ('{ "A": { "B": [ { "C": 0, 
"D": 2 }, { "C": 3, "D": 4 }, { "C": 0, "D": 6, "E": 3 } ] } }');
     INSERT 0 1

Il record 1 matcha, il 2 no. Puoi usare:

     piro=# select d.* from mydata d
     where exists (
         select 1 from (
             select id,
                 (json_populate_recordset(null::tmp, data -> 'A' -> 
'B'))."C"
             from mydata) x
         where "C" = 1
         and x.id = d.id);
      id |                                   data
     
----+---------------------------------------------------------------------------
       1 | { "A": { "B": [ { "C": 1, "D": 2 }, { "C": 3, "D": 4 }, { 
"C":1, "D": 6, .
         |."E": 3 } ] } }
     (1 row)

Non credo sia possibile ottimizzare questo tipo di query con un indice 
comunque. Ma quello neanche con un array sarebbe stato agevole.

-- Daniele