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/orSEGMENT_ID
Column Name | Description |
---|---|
| The name of the related database. |
| The name of the related table. |
| The name of the related column. |
| The partition’s number or placement among all the partitions. |
| 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 |
| The ID of a given segment. |
| Refers to which page within the JSON encoding is being described. |
| The number of rows in a given segment. |
| 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