Map JSON to columns and rows in PostgreSQL

Question

I'm trying to map JSON data to columns. Everything I need is contained in data array. Example:

{"data":
    [
     {"stamp":1348249585,"date":"2012-09-21 17:46","blur":"blurs/1.jpg","img":["imgs/1.jpg",[1600,1200]],"thumb":["thumbs/1.jpg",[150,113]]},
     {"stamp":1375607177,"date":"2013-08-04 09:06","blur":"blurs/2.jpg","img":["imgs/2.jpg",[1600,1200]],"thumb":["thumbs/2.jpg",[150,113]]},
     {"stamp":1376242046,"date":"2013-08-11 17:27","blur":"blurs/3.jpg","img":["imgs/3.jpg",[1600,1200]],"thumb":["thumbs/3.jpg",[150,113]]},
     ...

Currently, I am using #>> operator with dynamically generated condition:

1) Calculate number of elements in data array

2) Create varchar array condition to match every "row"

3) Process elements on individual rows.

My solution is:

select 
  json_row,
  json_row#>>'{stamp}' as stamp,
  json_row#>>'{date}' as date,  
  json_row#>>'{img,0}' as img,  
  json_row#>>'{img,1,0}' as img_width,    
  json_row#>>'{img,1,1}' as img_height,      
  json_row#>>'{thumb,0}' as thumb,
  json_row#>>'{thumb,1,0}' as thumb_width,  
  json_row#>>'{thumb,1,1}' as thumb_height,
  json_row#>>'{thumb,2,0}' as th_x1,  
  json_row#>>'{thumb,2,1}' as th_y1,    
  json_row#>>'{thumb,3,0}' as th_x2,      
  json_row#>>'{thumb,3,1}' as th_y2,        
  json_row#>>'{blur}'
from
  (
  select
    (gjson#>>c.cond)::json json_row
  from
    gallery_json 
    cross join (
      select ('{data,'|| generate_series(0,
        (select json_array_length((gjson#>>'{data}')::json) from gallery_json) - 1) || '}')::varchar[] cond) c
  ) rd

This works and I can live with it. But, given that this is my first exercise with JSON in PostgreSQL I would like to ask if there is better way to map similar JSON structure to rows. I think that I am supposed to use json_populate_recordset, but did not succeed so far.

SQLFiddle does not work currently, sample data:

--drop table if exists gallery_json;

create table gallery_json(gjson json);

insert into gallery_json (gjson) 
  select '{"data":[
    {"stamp":1348249585,"date":"2012-09-21 17:46","blur":"blurs/1.jpg","img":["imgs/1.jpg",[1600,1200]],"thumb":["thumbs/1.jpg",[150,113]]},
    {"stamp":1376659268,"date":"2013-08-16 13:21","blur":"blurs/7.jpg","img":["imgs/7.jpg",[1600,539]],"thumb":["thumbs/7.jpg",[267,112],[332,112],[32,0]]},
    {"stamp":1376666907,"date":"2013-08-16 15:28","blur":"blurs/8.jpg","img":["imgs/8.jpg",[1600,1200]],"thumb":["thumbs/8.jpg",[150,113]]},
    {"stamp":1379016669,"date":"2013-09-12 20:11","blur":"blurs/11.jpg","img":["imgs/11.jpg",[1600,590]],"thumb":["thumbs/11.jpg",[267,112],[304,112],[18,0]]},
    {"stamp":1383304027,"date":"2013-11-01 11:07","blur":"blurs/17.jpg","img":["imgs/17.jpg",[1600,1200]],"thumb":["thumbs/17.jpg",[150,113]]}]
    ,"blur":[600,336],"thumb":{"min":[150,112],"max":[267,200]}}'::json

Clodoaldo Neto · Accepted Answer · 2014-03-10 21:32:59Z

5

SQL Fiddle

with data as (
    select json_array_elements(gjson -> 'data') as data
    from gallery_json
)
select
    (data -> 'stamp')::text::bigint as stamp,
    (data -> 'date')::text::timestamp as date,
    (data -> 'blur')::text as blur,
    (data -> 'img' -> 0)::text as img,
    (data -> 'img' -> 1 -> 0)::text::int as img_width,
    (data -> 'img' -> 1 -> 1)::text::int as img_height,
    (data -> 'thumb' -> 0)::text as thumb,
    (data -> 'thumb' -> 1 -> 0)::text::int as thumb_width,
    (data -> 'thumb' -> 1 -> 1)::text::int as thumb_height,
    (data -> 'thumb' -> 2 -> 0)::text::int as th_x1,
    (data -> 'thumb' -> 2 -> 1)::text::int as th_y1,
    (data -> 'thumb' -> 3 -> 0)::text::int as th_x2,
    (data -> 'thumb' -> 3 -> 1)::text::int as th_y2
from data

answered Mar 10, 2014 at 21:32

Clodoaldo Neto

127k30 gold badges251 silver badges274 bronze badges

Sign up to request clarification or add additional context in comments.

Collectives™ on Stack Overflow

Map JSON to columns and rows in PostgreSQL

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related