Skip to main content

JSON_COLUMN_SCHEMA

This view contains information about the schema inferred for JSON columns in columnstore tables.

This schema view reads blobs from disk and fetches blobs from unlimited storage databases. Since reading data from blobs can be slow, using a filter on one or more of these fields will improve performance:

  • DATABASE_NAME,

  • TABLE_NAME,

  • COLUMN_NAME, and/or

  • SEGMENT_ID

Column Name

Description

DATABASE_NAME

The name of the related database.

TABLE_NAME

The name of the related table.

COLUMN_NAME

The name of the related column.

PARTITION_ORD

The partition’s number or placement among all the partitions.

SCHEMA_JSON

The schema inferred for the JSON column in a segment. The field will be in JSON format and contain details about the type of inferred columns. When the encoding is SeekableJSON, this field will contain information about the size and encoding of sub-components stored for each page within the encoding. When the encoding is JSONParquet, it will not have this information, and the page_id will always be 0.

SEGMENT_ID

The ID of a given segment.

PAGE_ID

Refers to which page within the JSON encoding is being described.

ROWS_COUNT

The number of rows in a given segment.

ENCODING

Refers to which encoding is used to store the JSON column for a segment (i.e., JSONParquet or SeekableJSON).

Example

Below is a sample output from the json_column_schema view. The SCHEMA_JSON field has been formatted to make it easier to read.

CREATE DATABASE examples;
USE examples;
CREATE TABLE json_exp(col_a json, sort KEY());
INSERT INTO json_exp VALUES('{"alpha": 1}'),('{"alpha": 1, "beta": "gamma"}');

OPTIMIZE TABLE json_exp FULL;

SELECT * FROM information_schema.json_column_schema WHERE database_name = 'examples'\G

*************************** 1. row ***************************
DATABASE_NAME: examples
   TABLE_NAME: json_exp
  COLUMN_NAME: col_a
PARTITION_ORD: 2
  SCHEMA_JSON: {"JSON_DOC":{
  		    "RepetitionType":"Required",
  		    "data":{
  		    "RepetitionType":"Required",
  		"alpha":{
  		    "CompressedSize":12,
  		    "DefinitionLevelEncoding":"Integer",
  		    "DefinitionLevelSize":4,
  		    "NumNulls":0,
  		    "NumValues":1,
  		    "RepetitionLevelEncoding":"Integer",
  		    "RepetitionLevelSize":4,
  		    "RepetitionType":"Required",
  	            "Type":"Int64",
  		    "UncompressedSize":12,
  		    "ValueEncoding":"Integer"}}}}
   SEGMENT_ID: 1
   SEGMENT_ID: 3
      PAGE_ID: 0
   ROWS_COUNT: 1
     ENCODING: SeekableJSON
*************************** 2. row ***************************
DATABASE_NAME: examples
   TABLE_NAME: json_exp
  COLUMN_NAME: col_a
PARTITION_ORD: 3
  SCHEMA_JSON: {"JSON_DOC":{
  		    "RepetitionType":"Required",
  		    "data":{
  		    "RepetitionType":"Required",
  		"alpha":{
  		    "CompressedSize":12,
  		    "DefinitionLevelEncoding":"Integer",
  		    "DefinitionLevelSize":4,
  		    "NumNulls":0,
  		    "NumValues":1,
  		    "RepetitionLevelEncoding":"Integer",
  		    "RepetitionLevelSize":4,
  		    "RepetitionType":"Required",
  		    "Type":"Int64",
  		    "UncompressedSize":12,
  		    "ValueEncoding":"Integer"},
                "beta":{
  		    "CompressedSize":33,
  		    "DefinitionLevelEncoding":"Integer",
  		    "DefinitionLevelSize":4,
  		    "NumNulls":0,
  		    "NumValues":1,
  		    "RepetitionLevelEncoding":"Integer",
  		    "RepetitionLevelSize":4,
  		    "RepetitionType":"Required",
  		    "Type":"ByteArray",
  		    "UncompressedSize":33,
  		    "ValueEncoding":"SeekableString"}}}}	
   SEGMENT_ID: 1
      PAGE_ID: 1
   ROWS_COUNT: 1
     ENCODING: SeekableJSON
In this section: