# Serializing with JSON

## Setup

The following describes through examples how a DeltaStream query converts JSON payloads to DeltaStream’s [Data Types](/reference/sql-syntax/data-types.md) when reading from a [Database](/overview/core-concepts/databases.md#_stream) or [Database](/overview/core-concepts/databases.md#_changelog).

The following examples use the stream defined below:

```sql
CREATE STREAM jsonExample (
  "booleanValue" BOOLEAN,
  "stringValue" VARCHAR,
  "tinyIntValue" TINYINT,
  "smallIntValue" SMALLINT,
  "intValue" INTEGER,
  "bigIntValue" BIGINT,
  "floatValue" FLOAT,
  "doubleValue" DOUBLE,
  "decimalValue" DECIMAL(4, 3),
  "dateValue" DATE,
  "timeValue" TIME,
  "timestampValue" TIMESTAMP(3),
  "timestampLtzValue" TIMESTAMP_LTZ,
  "bytesValue" VARBINARY,
  "arrayValue" ARRAY<VARCHAR>,
  "mapValue" MAP<VARCHAR, BIGINT>,
  "structValue" STRUCT<col1 BIGINT>
) WITH (
  'topic' = 'jsonExample', 'value.format' = 'JSON'
);
```

## Simple Example

With the query:

```sql
SELECT * FROM jsonExample;
```

<pre class="language-json"><code class="lang-json"><strong>// input record
</strong><strong>{
</strong>  "booleanValue": true,
  "stringValue": "howdy",
  "tinyIntValue": 1,
  "smallIntValue": 12,
  "intValue": 1234,
  "bigIntValue": 123456789,
  "floatValue": 12.34,
  "doubleValue": 1234.5678,
  "decimalValue": 1.123,
  "dateValue": "2019-12-26",
  "timeValue": "16:15:14",
  "timestampValue": "2011-12-03 10:15:30",
  "timestampLtzValue": "2021-05-31 16:15:14.528Z",
  "bytesValue": "aG93ZHk=",
  "arrayValue": [
    "News",
    "Travel"
  ],
  "mapValue": {
    "count": 17
  },
  "structValue": {
    "col1": 1234
  }
}
</code></pre>

```json
// output record
{
  "booleanValue": true,
  "stringValue": "howdy",
  "tinyIntValue": 1,
  "smallIntValue": 12,
  "intValue": 1234,
  "bigIntValue": 123456789,
  "floatValue": 12.34,
  "doubleValue": 1234.5678,
  "decimalValue": 1.123,
  "dateValue": "2019-12-26",
  "timeValue": "16:15:14",
  "timestampValue": "2011-12-03 10:15:30",
  "timestampLtzValue": "2021-05-31 16:15:14.528Z",
  "bytesValue": "aG93ZHk=",
  "arrayValue": [
    "News",
    "Travel"
  ],
  "mapValue": {
    "count": 17
  },
  "structValue": {
    "col1": 1234
  }
}
```

## Partial Record Example

When JSON records are missing fields specified by the `CREATE STREAM DDL` statement, those fields are given the value `NULL` in the output record. In the opposite case — when JSON records have fields that aren’t specified by the `CREATE STREAM DDL` statement — those fields are ignored.

With the query:

```sql
SELECT "booleanValue", "stringValue", "intValue" FROM jsonExample;
```

```json
// input record
{
  "booleanValue": true,
  "stringValue": "howdy",
  "someOtherValue": 123
}

// output record
{
  "booleanValue": true,
  "stringValue": "howdy",
  "intValue": null
}
```

## Mismatched Types Example

### Boolean

With the query:

```sql
SELECT "booleanValue" FROM jsonExample;
```

```json
// input record
{ "booleanValue": true }

//output record
{ "booleanValue": true }
```

```json
// input record
{ "booleanValue": false }

//output record
{ "booleanValue": false }
```

```json
// input record
{ "booleanValue": "true" }

//output record
{ "booleanValue": true }
```

```json
// input record
{ "booleanValue": "false" }

//output record
{ "booleanValue": false }
```

```json
// input record
{ "booleanValue": "abc" }

//output record
{ "booleanValue": false }
```

```json
// input record
{ "booleanValue": 123 }

//output record
{ "booleanValue": false }
```

### Character String

With the query:

```sql
SELECT "stringValue" FROM jsonExample;
```

```json
// input record
{ "stringValue": "abc" }

//output record
{ "stringValue": "abc" }
```

```json
// input record
{ "stringValue": true }

//output record
{ "stringValue": "true" }
```

```json
// input record
{ "stringValue": 123 }

//output record
{ "stringValue": "123" }
```

```json
// input record
{ "stringValue": 123.456 }

//output record
{ "stringValue": "123.456" }
```

### Numeric

With the query:

```sql
SELECT "tinyIntValue", "doubleValue", "decimalValue" FROM jsonExample;
```

```json
// input record
{ "tinyIntValue": 1, "doubleValue": 123.1, "decimalValue": 1.123 }

// output record
{ "tinyIntValue": 1, "doubleValue": 123.1, "decimalValue": 1.123 }
```

```json
// input record
{ "tinyIntValue": "1", "doubleValue": "123.1", "decimalValue": "1.123" }

// output record
{ "tinyIntValue": 1, "doubleValue": 123.1, "decimalValue": 1.123 }
```

```json
// input record ("decimalValue" value is larger than defined precision)
{ "tinyIntValue": 1, "doubleValue": 123.1, "decimalValue": 12.123 }

// output record
{ "tinyIntValue": 1, "doubleValue": 123.1, "decimalValue": null }
```

```json
// input record
{ "tinyIntValue": "abc", "doubleValue": 123.1, "decimalValue": 1.123 }

// deserialization error because String can't be cast to numeric value
```

```json
// input record
{ "tinyIntValue": 130, "doubleValue": 123.1, "decimalValue": 1.123 }

// deserialization error because 130 is out of range for TINYINT values
```

```json
// input record
{ "tinyIntValue": 1.1, "doubleValue": 123.1, "decimalValue": 1.123 }

// deserialization error because TINYINT cannot be a floating point value
```

### Date and Time

```sql
SELECT 
  "dateValue", 
  "timeValue", 
  "timestampValue", 
  "timestampLtzValue" 
FROM 
  jsonExample;
```

```json
// input record
{
  "dateValue": "2019-12-26",
  "timeValue": "16:15:14",
  "timestampValue": "2011-12-03 10:15:30",
  "timestampLtzValue": "2021-05-31 16:15:14.528Z"
}

// output record
{
  "dateValue": "2019-12-26",
  "timeValue": "16:15:14",
  "timestampValue": "2011-12-03 10:15:30",
  "timestampLtzValue": "2021-05-31 16:15:14.528Z"
}
```

```json
// input record
{ "dateValue": 1234 }

// deserialization error because date and time values must be parsed from Strings
```

```json
// input record
{ "dateValue": "2019-04-31" }

// deserialization error due to invalid date (April only has 30 days)
```

### Binary String

With the query:

```sql
SELECT "bytesValue" FROM jsonExample;
```

```json
// input record
{ "bytesValue": "aG93ZHk=" }

// output record
{ "bytesValue": "aG93ZHk=" }
```

```json
// input record
{ "bytesValue": 1 }

// deserialization error because we expect a String value
```

### Constructed Data Types

With the query:

```sql
SELECT "arrayValue", "mapValue", "structValue" FROM jsonExample;
```

```json
// input record
{
  "arrayValue": [
    "News",
    "Travel"
  ],
  "mapValue": {
    "count": 17
  },
  "structValue": {
    "col1": 1234
  }
}

// output record
{
  "arrayValue": [
    "News",
    "Travel"
  ],
  "mapValue": {
    "count": 17
  },
  "structValue": {
    "col1": 1234
  }
}
```

#### Array

```json
// input record
{ "arrayValue": [] }

// output record
{
  "arrayValue": [],
  "mapValue": null,
  "structValue": null
}
```

```json
// input record
{
  "arrayValue": [ 17 ]
}

// output record
{
  "arrayValue": [ "17" ],
  "mapValue": null,
  "structValue": null
}
```

<pre class="language-json"><code class="lang-json">// input record
{
<strong>  "arrayValue": []
</strong><strong>}
</strong>
// output record
{
  "arrayValue": [],
  "mapValue": null,
  "structValue": null
}
</code></pre>

#### Map

```json
// input record
{
  "mapValue": {
    "count": 17,
    "index": 102
  }
}

// output record
{
  "arrayValue": null,
  "mapValue": {
    "count": 17,
    "index": 102
  },
  "structValue": null
}
```

```json
// input record
{
  "mapValue": {
    "count": "howdy"
  }
}

// deserialization error because String can't be cast to numeric value
```

```json
// input record
{
  "mapValue": {}
}

// output record
{
  "arrayValue": null,
  "mapValue": {},
  "structValue": null
}
```

#### Struct

```json
// input record
{
  "structValue": {
    "col1": 1234,
    "col2": 5678
  }
}

// output record
{
  "arrayValue": null,
  "mapValue": null,
  "structValue": {
    "col1": 1234
  }
}
```

```json
// input record
{
  "structValue": {
    "col2": 5678
  }
}

// output record
{
  "arrayValue": null,
  "mapValue": null,
  "structValue": {
    "col1": null
  }
}
```

```json
// input record
{
  "structValue": {}
}

// output record
{
  "arrayValue": null,
  "mapValue": null,
  "structValue": {
    "col1": null
  }
}
```


---

# Agent Instructions: Querying This Documentation

If you need additional information that is not directly available in this page, you can query the documentation dynamically by asking a question.

Perform an HTTP GET request on the current page URL with the `ask` query parameter:

```
GET https://docs.deltastream.io/reference/sql-syntax/data-format-serialization/serde-with-json-data.md?ask=<question>
```

The question should be specific, self-contained, and written in natural language.
The response will contain a direct answer to the question and relevant excerpts and sources from the documentation.

Use this mechanism when the answer is not explicitly present in the current page, you need clarification or additional context, or you want to retrieve related documentation sections.