24.6 Specifying a Preferred Name for a Field Column

You can project JSON fields from your data as non-JSON columns in a database view or as non-JSON virtual columns added to the same table that contains the JSON column. You can specify a preferred name for such a column.

The document fields are projected as columns when you use procedure DBMS_JSON.create_view, DBMS_JSON.create_view_on_path, or DBMS_JSON.add_virtual_columns.

A data guide obtained from your JSON document set is used to define this projection. The name of each projected column is taken from data-guide field o:preferred_column_name for the JSON data field to be projected.

If your JSON data has a data guide-enabled search index then you can use procedure DBMS_JSON.rename_column to set the value of o:preferred_column_name for a given document field and type. Example 24-4 illustrates this. It specifies preferred names for the columns to be projected from various fields, as described in Table 24-4.

A hierarchical or schema data guide is populated with field o:preferred_column_name. When you use procedure DBMS_JSON.create_view or DBMS_JSON.add_virtual_columns, you can pass parameters that further control the naming of projected columns:

  • colNamePrefix => prefix — Prefix the names specified by o:preferred_column_name with prefix.

  • mixedCaseColumns => FALSE — Make column names be case-insensitive. (They are case-sensitive by default.)

  • resolveNameConflicts => TRUE (default) — Resolve any name conflicts.

Table 24-4 Preferred Names for Some JSON Field Columns

Field JSON Type Preferred Column Name
PONumber number PONumber
Phone (phone as string, not object – just the number) string Phone
type (phone type) string PhoneType
number (phone number) string PhoneNumber
ItemNumber (line-item number) number ItemNumber
Description (part description) string PartDescription

See Also:

Example 24-4 Specifying Preferred Column Names For Some JSON Fields

BEGIN
  DBMS_JSON.rename_column(
    'J_PURCHASEORDER', 'DATA',
    '$.PONumber',
    DBMS_JSON.TYPE_NUMBER, 'PONumber');
  DBMS_JSON.rename_column(
    'J_PURCHASEORDER', 'DATA',
    '$.ShippingInstructions.Phone',
    DBMS_JSON.TYPE_STRING, 'Phone');
  DBMS_JSON.rename_column(
    'J_PURCHASEORDER', 'DATA',
    '$.ShippingInstructions.Phone.type',
    DBMS_JSON.TYPE_STRING, 'PhoneType');
  DBMS_JSON.rename_column(
    'J_PURCHASEORDER', 'DATA',
    '$.ShippingInstructions.Phone.number',
    DBMS_JSON.TYPE_STRING, 'PhoneNumber');
  DBMS_JSON.rename_column(
    'J_PURCHASEORDER', 'DATA',
    '$.LineItems.ItemNumber',
    DBMS_JSON.TYPE_NUMBER, 'ItemNumber');
  DBMS_JSON.rename_column(
    'J_PURCHASEORDER', 'DATA',
    '$.LineItems.Part.Description',
    DBMS_JSON.TYPE_STRING, 'PartDescription');
END;
/

Parent topic: JSON Data Guide