JDBC Driver for Cloudant

Build 22.0.8462

Vertical Flattening

It is possible to retrieve an array of documents as if it were a separate table. Take the following JSON structure from the restaurants collection for example:

{
  "_id" : ObjectId("568c37b748ddf53c5ed98932"),
  "address" : {
    "building" : "1007",
    "coord" : [-73.856077, 40.848447],
    "street" : "Morris Park Ave",
    "zipcode" : "10462"
  },
  "borough" : "Bronx",
  "cuisine" : "Bakery",
  "grades" : [{
      "date" : ISODate("2014-03-03T00:00:00Z"),
      "grade" : "A",
      "score" : 2
    }, {
      "date" : ISODate("2013-09-11T00:00:00Z"),
      "grade" : "A",
      "score" : 6
    }, {
      "date" : ISODate("2013-01-24T00:00:00Z"),
      "grade" : "A",
      "score" : 10
    }, {
      "date" : ISODate("2011-11-23T00:00:00Z"),
      "grade" : "A",
      "score" : 9
    }, {
      "date" : ISODate("2011-03-10T00:00:00Z"),
      "grade" : "B",
      "score" : 14
    }],
  "name" : "Morris Park Bake Shop",
  "restaurant_id" : "30075445"
}
Vertical flattening will allow you to retrieve the grades array as a separate table:
SELECT * FROM [restaurants.grades]
This query returns the following data set:

dategradescoreP_id_index
2014-03-03T00:00:00.000ZA2568c37b748ddf53c5ed989321
2013-09-11T00:00:00.000ZA6568c37b748ddf53c5ed989322
2013-01-24T00:00:00.000ZA10568c37b748ddf53c5ed989323

You may also want to include information from the base restaurants table. You can do this with a join. Flattened arrays can only be joined with the root document. The driver expects the left part of the join is the array document you want to flatten vertically. Disable SupportEnhancedSQL to join nested Cloudant documents -- this type of query is supported through the Cloudant API.

SELECT [restaurants].[restaurant_id], [restaurants.grades].* FROM [restaurants.grades] JOIN [restaurants] WHERE [restaurants].name = 'Morris Park Bake Shop'
This query returns the following data set:

restaurant_iddategradescoreP_id_index
300754452014-03-03T00:00:00.000ZA2568c37b748ddf53c5ed989321
300754452013-09-11T00:00:00.000ZA6568c37b748ddf53c5ed989322
300754452013-01-24T00:00:00.000ZA10568c37b748ddf53c5ed989323
300754452011-11-23T00:00:00.000ZA9568c37b748ddf53c5ed989324
300754452011-03-10T00:00:00.000ZB14568c37b748ddf53c5ed989325

Copyright (c) 2023 CData Software, Inc. - All rights reserved.
Build 22.0.8462