# Serializing with JSON

## Setup

The following describes through examples how a DeltaStream query converts JSON payloads to DeltaStream’s [data-types](https://docs.deltastream.io/reference/sql-syntax/data-types "mention") when reading from a [#\_stream](https://docs.deltastream.io/overview/core-concepts/databases#_stream "mention") or [#\_changelog](https://docs.deltastream.io/overview/core-concepts/databases#_changelog "mention").

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