Interrogazione per elementi di array all’interno del tipo JSON

Sto provando a testare il tipo json in PostgreSQL 9.3.
Ho una colonna json chiamata data in una tabella chiamata reports . Il JSON assomiglia a questo:

 { "objects": [ {"src":"foo.png"}, {"src":"bar.png"} ], "background":"background.png" } 

Vorrei interrogare la tabella per tutti i report che corrispondono al valore ‘src’ nella matrice ‘objects’. Ad esempio, è ansible interrogare il DB per tutti i report che corrispondono a 'src' = 'foo.png' ? Ho scritto con successo una query che può corrispondere allo "background" :

 SELECT data AS data FROM reports where data->>'background' = 'background.png' 

Ma dal momento che "objects" ha una serie di valori, non riesco a scrivere qualcosa che funzioni. È ansible interrogare il DB per tutti i report che corrispondono a 'src' = 'foo.png' ? Ho esaminato queste fonti ma ancora non riesco a ottenerle:

  • http://www.postgresql.org/docs/9.3/static/functions-json.html
  • Come posso interrogare usando i campi all’interno del nuovo tipo di dati JSON PostgreSQL?
  • http://michael.otacoo.com/postgresql-2/postgres-9-3-feature-highlight-json-operators/

Ho anche provato cose come questa, ma senza alcun risultato:

 SELECT json_array_elements(data->'objects') AS data from reports WHERE data->>'src' = 'foo.png'; 

Non sono un esperto di SQL, quindi non so cosa sto facendo male.

    json in Postgres 9.3+

    Unnest l’array JSON con la funzione json_array_elements() in un join laterale nella clausola FROM e verifica i suoi elementi:

     WITH reports(data) AS ( VALUES ('{"objects":[{"src":"foo.png"}, {"src":"bar.png"}] , "background":"background.png"}'::json) ) SELECT * FROM reports r, json_array_elements(r.data#>'{objects}') obj WHERE obj->>'src' = 'foo.png'; 

    Il CTE (query WITH ) sostituisce semplicemente i reports una tabella.
    Oppure, equivalente per un solo livello di nidificazione:

     SELECT * FROM reports r, json_array_elements(r.data->'objects') obj WHERE obj->>'src' = 'foo.png'; 

    ->> operatori ->> , -> e #> sono spiegati nel manuale.

    Entrambe le query utilizzano un implicito JOIN LATERAL .

    SQL Fiddle.

    Risposta strettamente correlata:

    • Query per elemento di array nella colonna JSON

    jsonb in Postgres 9.4+

    Usa l’equivalente jsonb_array_elements() .

    Meglio ancora, usa il nuovo operatore “contiene” @> (meglio in combinazione con un indice GIN corrispondente sui data->'objects' dell’espressione data->'objects' ):

     CREATE INDEX reports_data_gin_idx ON reports USING gin ((data->'objects') jsonb_path_ops); SELECT * FROM reports WHERE data->'objects' @> '[{"src":"foo.png"}]'; 

    Poiché gli objects chiave contengono un array JSON, dobbiamo abbinare la struttura nel termine di ricerca e avvolgere anche l’elemento dell’array in parentesi quadre. Rilascia le parentesi dell’array durante la ricerca di un record normale.

    Spiegazione dettagliata e più opzioni:

    • Indice per trovare un elemento in un array JSON

    Crea una tabella con colonna come tipo json

     # CREATE TABLE friends ( id serial primary key, data jsonb); 

    Ora inseriamo i dati JSON

     # INSERT INTO friends(data) VALUES ('{"name": "Arya", "work": ["Improvements", "Office"], "available": true}'); # INSERT INTO friends(data) VALUES ('{"name": "Tim Cook", "work": ["Cook", "ceo", "Play"], "uses": ["baseball", "laptop"], "available": false}'); 

    Ora facciamo alcune query per recuperare i dati

     # select data->'name' from friends; # select data->'name' as name, data->'work' as work from friends; 

    Potresti aver notato che i risultati sono forniti con virgola invertita (“) e parentesi ([])

      name | work ------------+---------------------------- "Arya" | ["Improvements", "Office"] "Tim Cook" | ["Cook", "ceo", "Play"] (2 rows) 

    Ora per recuperare solo i valori basta usare ->>

     # select data->>'name' as name, data->'work'->>0 as work from friends; #select data->>'name' as name, data->'work'->>0 as work from friends where data->>'name'='Arya';