A Hierarchical Data Guide For Purchase-Order Documents

24.13 A Hierarchical Data Guide For Purchase-Order Documents

The fields of a sample hierarchical data guide are described. It corresponds to a set of purchase-order documents.

Example 24-23 shows a hierarchical data guide for the purchase-order documents in table j_purchaseorder. The data guide was created using procedure DBMS_JSON.get_index_dataguide.

Example 24-23 Hierarchical Data Guide For Purchase Orders

Field names are bold. JSON Schema keywords are italic. Preferred column names that result from using DBMS_JSON.rename_column are also italic. The formatting used is similar to that produced by using SQL/JSON function json_dataguide with format arguments DBMS_JSON.FORMAT_HIERARCHICAL and DBMS_JSON.PRETTY.

Note that statistical fields o:frequency, o:low_value, o:high_value, o:num_nulls, and o:last_analyzed are present in this example. This can only be because statistics were gathered on the document set. Their values reflect the state as of the last statistics gathering. See Example 24-3 for an example of gathering statistics for this data.

A hierarchical data guide created by SQL function json_dataguide would look similar to this example, but with these differences:

The values of field o:preferred_column_name would the same as the field names in your JSON documents. That is, they would not be prefixed with DATA$.
Statistical fields would be present only if json_dataguide were invoked with DBMS_JSON.GATHER_STATS in its third argument. And in this case field o:sample_size would also be present, following field o:last_analyzed. (The value of o:sample_size would be 2 if there are two documents in the queried column of JSON data.)

{
  "type": "object",
  "properties": {
    "User": {
      "type": "string",
      "o:length": 8,
      "o:preferred_column_name": "DATA$User",
      "o:frequency": 100,
      "o:low_value": "ABULL",
      "o:high_value": "SBELL",
      "o:num_nulls": 0,
      "o:last_analyzed": "2016-03-31T12:17:53"
    },
    "PONumber": {
      "type": "number",
      "o:length": 4,
      "o:preferred_column_name": "PONumber",
      "o:frequency": 100,
      "o:low_value": "672",
      "o:high_value": "1600",
      "o:num_nulls": 0,
      "o:last_analyzed": "2016-03-31T12:17:53"
    },
    "LineItems": {
      "type": "array",
      "o:length": 512,
      "o:preferred_column_name": "DATA$LineItems",
      "o:frequency": 100,
      "o:last_analyzed": "2016-03-31T12:17:53",
      "items": {
        "properties": {
          "Part": {
            "type": "object",
            "o:length": 128,
            "o:preferred_column_name": "DATA$Part",
            "o:frequency": 100,
            "o:last_analyzed": "2016-03-31T12:17:53",
            "properties": {
              "UPCCode": {
                "type": "number",
                "o:length": 16,
                "o:preferred_column_name": "DATA$UPCCode",
                "o:frequency": 100,
                "o:low_value": "13131092899",
                "o:high_value": "717951002396",
                "o:num_nulls": 0,
                "o:last_analyzed": "2016-03-31T12:17:53"
              },
              "UnitPrice": {
                "type": "number",
                "o:length": 8,
                "o:preferred_column_name": "DATA$UnitPrice",
                "o:frequency": 100,
                "o:low_value": "20",
                "o:high_value": "19.95",
                "o:num_nulls": 0,
                "o:last_analyzed": "2016-03-31T12:17:53"
              },
              "Description": {
                "type": "string",
                "o:length": 32,
                "o:preferred_column_name": "PartDescription",
                "o:frequency": 100,
                "o:low_value": "Nixon",
                "o:high_value": "Eric Clapton: Best Of 1981-1999",
                "o:num_nulls": 0,
                "o:last_analyzed": "2016-03-31T12:17:53"
              }
            }
          },
          "Quantity": {
            "type": "number",
            "o:length": 4,
            "o:preferred_column_name": "DATA$Quantity",
            "o:frequency": 100,
            "o:low_value": "5",
            "o:high_value": "9.0",
            "o:num_nulls": 0,
            "o:last_analyzed": "2016-03-31T12:17:53"
          },
          "ItemNumber": {
            "type": "number",
            "o:length": 1,
            "o:preferred_column_name": "ItemNumber",
            "o:frequency": 100,
            "o:low_value": "1",
            "o:high_value": "3",
            "o:num_nulls": 0,
            "o:last_analyzed": "2016-03-31T12:17:53"
          }
        }
      }
    },
    "Reference": {
      "type": "string",
      "o:length": 16,
      "o:preferred_column_name": "DATA$Reference",
      "o:frequency": 100,
      "o:low_value": "ABULL-20140421",
      "o:high_value": "SBELL-20141017",
      "o:num_nulls": 0,
      "o:last_analyzed": "2016-03-31T12:17:53"
    },
    "Requestor": {
      "type": "string",
      "o:length": 16,
      "o:preferred_column_name": "DATA$Requestor",
      "o:frequency": 100,
      "o:low_value": "Sarah Bell",
      "o:high_value": "Alexis Bull",
      "o:num_nulls": 0,
      "o:last_analyzed": "2016-03-31T12:17:53"
    },
    "CostCenter": {
      "type": "string",
      "o:length": 4,
      "o:preferred_column_name": "DATA$CostCenter",
      "o:frequency": 100,
      "o:low_value": "A50",
      "o:high_value": "A50",
      "o:num_nulls": 0,
      "o:last_analyzed": "2016-03-31T12:17:53"
    },
    "AllowPartialShipment": {
      "type": "boolean",
      "o:length": 4,
      "o:preferred_column_name": "DATA$AllowPartialShipment",
      "o:frequency": 50,
      "o:last_analyzed": "2016-03-31T12:17:53"
    },
    "ShippingInstructions": {
      "type": "object",
      "o:length": 256,
      "o:preferred_column_name": "DATA$ShippingInstructions",
      "o:frequency": 100,
      "o:last_analyzed": "2016-03-31T12:17:53",
      "properties": {
        "name": {
          "type": "string",
          "o:length": 16,
          "o:preferred_column_name": "DATA$name",
          "o:frequency": 100,
          "o:low_value": "Sarah Bell",
          "o:high_value": "Alexis Bull",
          "o:num_nulls": 0,
          "o:last_analyzed": "2016-03-31T12:17:53"
        },
        "Phone": {
          "oneOf": [
            {
              "type": "string",
              "o:length": 16,
              "o:preferred_column_name": "Phone",
              "o:frequency": 50,
              "o:low_value": "983-555-6509",
              "o:high_value": "983-555-6509",
              "o:num_nulls": 0,
              "o:last_analyzed": "2016-03-31T12:17:53"
            },
            {
              "type": "array",
              "o:length": 128,
              "o:preferred_column_name": "DATA$Phone_1",
              "o:frequency": 50,
              "o:last_analyzed": "2016-03-31T12:17:53",
              "items": {
                "properties": {
                  "type": {
                    "type": "string",
                    "o:length": 8,
                    "o:preferred_column_name": "PhoneType",
                    "o:frequency": 50,
                    "o:low_value": "Mobile",
                    "o:high_value": "Office",
                    "o:num_nulls": 0,
                    "o:last_analyzed": "2016-03-31T12:17:53"
                  },
                  "number": {
                    "type": "string",
                    "o:length": 16,
                    "o:preferred_column_name": "PhoneNumber",
                    "o:frequency": 50,
                    "o:low_value": "415-555-1234",
                    "o:high_value": "909-555-7307",
                    "o:num_nulls": 0,
                    "o:last_analyzed": "2016-03-31T12:17:53"
                  }
                }
              }
            }
          ]
        },
        "Address": {
          "type": "object",
          "o:length": 128,
          "o:preferred_column_name": "DATA$Address",
          "o:frequency": 100,
          "o:last_analyzed": "2016-03-31T12:17:53",
          "properties": {
            "city": {
              "type": "string",
              "o:length": 32,
              "o:preferred_column_name": "DATA$city",
              "o:frequency": 100,
              "o:low_value": "South San Francisco",
              "o:high_value": "South San Francisco",
              "o:num_nulls": 0,
              "o:last_analyzed": "2016-03-31T12:17:53"
            },
            "state": {
              "type": "string",
              "o:length": 2,
              "o:preferred_column_name": "DATA$state",
              "o:frequency": 100,
              "o:low_value": "CA",
              "o:high_value": "CA",
              "o:num_nulls": 0,
              "o:last_analyzed": "2016-03-31T12:17:53"
            },
            "street": {
              "type": "string",
              "o:length": 32,
              "o:preferred_column_name": "DATA$street",
              "o:frequency": 100,
              "o:low_value": "200 Sporting Green",
              "o:high_value": "200 Sporting Green",
              "o:num_nulls": 0,
              "o:last_analyzed": "2016-03-31T12:17:53"
            },
            "country": {
              "type": "string",
              "o:length": 32,
              "o:preferred_column_name": "DATA$country",
              "o:frequency": 100,
              "o:low_value": "United States of America",
              "o:high_value": "United States of America",
              "o:num_nulls": 0,
              "o:last_analyzed": "2016-03-31T12:17:53"
            },
            "zipCode": {
              "type": "number",
              "o:length": 8,
              "o:preferred_column_name": "DATA$zipCode",
              "o:frequency": 100,
              "o:low_value": "99236",
              "o:high_value": "99236",
              "o:num_nulls": 0,
              "o:last_analyzed": "2016-03-31T12:17:53"
            }
          }
        }
      }
    },
    "Special Instructions": {
      "type": "string",
      "o:length": 8,
      "o:preferred_column_name": "DATA$SpecialInstructions",
      "o:frequency": 100,
      "o:low_value": "Courier",
      "o:high_value": "Courier",
      "o:num_nulls": 1,
      "o:last_analyzed": "2016-03-31T12:17:53"
    }
  }
}