# 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).                                                                                                                                                                                                                                                                                                         |
| `numInferredPaths` | The number of key paths inferred for the segment.                                                                                                                                                                                                                                                                                                                                                                    |
| schemaSize         | The size of`schema_json`in bytes.                                                                                                                                                                                                                                                                                                                                                                                    |

## 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.

```sql
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


```

```output
*** 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": {
  		    "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

```

***

Modified at: April 28, 2025

Source: [/db/v9.1/reference/information-schema-reference/segment-column-and-merge-status/json-column-schema/](https://docs.singlestore.com/db/v9.1/reference/information-schema-reference/segment-column-and-merge-status/json-column-schema/)

(An index of the documentation is available at /llms.txt)