Loading contents of json array in redshift

Question

I'm setting up redshift and importing data from mongo. I have succeeded in using a json path file for a simple document but am now needing to import from a document containing an array.

{
   "id":123,
   "things":[
      {
         "foo":321,
         "bar":654
      },
      {
         "foo":987,
         "bar":567
      }
   ]
}

How do I load the above in to a table like so:

select * from things;

    id  | foo  | bar
--------+------+-------
   123  | 321  | 654
   123  | 987  | 567

or is there some other way?

I can't just store the json array in a varchar(max) column as the content of Things can exceed 64K.

danielfishr · Accepted Answer · 2014-07-01 17:23:24Z

1

Given

db.baz.insert({
   "myid":123,
   "things":[
      {
         "foo":321,
         "bar":654
      },
      {
         "foo":987,
         "bar":567
      }
   ]
});

The following will display the fields you want

db.baz.find({},{"things.foo":1,"things.bar":1} )

To flatten the result set use aggregation like so

 db.baz.aggregate( 
 {"$group": {"_id": "$myid", "things": { "$push" : {"foo":"$things.foo","bar":"$things.bar"}}}},
 {    
   $project : {
     _id:1,
     foo : "$things.foo",
     bar : "$things.bar"   
   } 
  },
  { "$unwind" : "$foo" },
  { "$unwind" : "$bar" }
);

edited Jul 1, 2014 at 17:23

answered Jul 1, 2014 at 15:42

danielfishr

8796 silver badges9 bronze badges

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

Collectives™ on Stack Overflow

Loading contents of json array in redshift

1 Answer 1

Comments

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Linked

Related