JDBC Driver for Cloudant

Build 22.0.8462

Free-Form Queries

As discussed in Automatic Schema Discovery, intuited table schemas enable SQL access to unstructured Cloudant data. JSON Functions enable you to use standard JSON functions to summarize Cloudant data and extract values from any nested structures. However, you are not limited to these schemes.

After connecting you can query any nested structure without flattening the data. Any relations that you can access with FlattenArrays and FlattenObjects can also be accessed with an ad hoc SQL query.

Let's consider an example document from the following Restaurant data set:

 
{
  "address": {
    "building": "1007",
    "coord": [
      -73.856077,
      40.848447
    ],
    "street": "Morris Park Ave",
    "zipcode": "10462"
  },
  "borough": "Bronx",
  "cuisine": "Bakery",
  "grades": [
    {
      "grade": "A",
      "score": 2,
      "date": {
        "$date": "1393804800000"
      }
    },
    {
      "date": {
        "$date": "1378857600000"
      },
      "grade": "B",
      "score": 6
    },
    {
      "score": 10,
      "date": {
        "$date": "1358985600000"
      },
      "grade": "C"
    }
  ],
  "name": "Morris Park Bake Shop",
  "restaurant_id": "30075445"
} 
You can access any nested structure in this document as a column. Use the dot notation to drill down to the values you want to access as shown in the query below. Note that arrays have a zero-based index. For example, the following query retrieves the second grade for the restaurant in the example:
SELECT [address.building], [grades.1.grade] FROM restaurants WHERE restaurant_id = '30075445'
The preceding query returns the following results:

Column NameData TypeExample Value
address.buildingString1007
grades.1.gradeStringA

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