CODEWITHSUNDEEP
amazon-redshift
Kevin Pluck
Kevin Pluck

Reputation: 681

Loading contents of json array in redshift

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.

Upvotes: 1

Views: 1264

Answers (1)

danielfishr
danielfishr

Reputation: 879

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" }
);

Upvotes: 1

Related Questions