json - 将 JSON 映射到 PostgreSQL 中的列和行

Question

我正在尝试将 JSON 数据映射到列。我需要的一切都包含在 data 数组中。示例:

{"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]]},
     ...

目前，我正在使用具有动态生成条件的 #>> 运算符:

1) 计算data数组中的元素个数

2) 创建 varchar 数组条件以匹配每个“行”

3) 处理单行上的元素。

我的解决方案是:

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

这行得通，我可以接受。但是，鉴于这是我在 PostgreSQL 中使用 JSON 进行的第一次练习，我想问一下是否有更好的方法将类似的 JSON 结构映射到行。我认为我应该使用 json_populate_recordset , 但到目前为止还没有成功。

SQLFiddle 当前不工作，示例数据:

--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

Answer 1

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