# Alternative data types

Data Shaping Query Language allows you to handle multiple data types for the same element, using an Avro schema. For more information about Avro schemas, see the [Oracle documentation](https://docs.oracle.com/cd/E26161_02/html/GettingStartedGuide/avroschemas.html).

In the following sample JSON input, the element order\_amount can be either:

* An integer
* A decimal
* A string
* A record containing two decimals, items\_total and tax\_amount

```
{
    "orders": [
        {
            "order_id": "abc-12345",
            "order_amount": 250
        },
        {
            "order_id": "def-67890",
            "order_amount": "299.99"
        },
        {
            "order_id": "ghi-54321",
            "order_amount": 435.95
        },
        {
            "order_id": "jkl-09876",
            "order_amount": {
                "items_total": 395.99,
                "tax_amount": 11.89
            }
        }
    ]
}
```

This input is described by the following Avro schema:

```
{
    "type": "record",
    "name": "root",
    "fields": [
        {
            "name": "orders",
            "type": {
                "type": "array",
                "items": {
                    "type": "record",
                    "name": "order",
                    "fields": [
                        {
                            "name": "order_id",
                            "type": "string"
                        },
                        {
                            "name": "order_amount",
                            "type": [
                                "int",
                                {
                                    "type": "bytes",
                                    "logicalType": "decimal"
                                },
                                "string",
                                {
                                    "type": "record",
                                    "name": "amount",
                                    "fields": [
                                        {
                                            "name": "items_total",
                                            "type": {
                                                "type": "bytes",
                                                "logicalType": "decimal"
                                            }
                                        },
                                        {
                                            "name": "tax_amount",
                                            "type": {
                                                "type": "bytes",
                                                "logicalType": "decimal"
                                            }
                                        }
                                    ]
                                }
                            ]
                        }
                    ]
                }
            }
        }
    ]
}
```

Data Shaping Query Language allows you reference each possible type by appending the type's index to the element's identifier using the following syntax: identifier.@index . In this example, int is the first value in the list of possible types for the order\_amount element, so it can be referenced with order\_amount.@0.

This syntax can also be used in a hierarchical identifier. For example, you can reference the tax\_amount element in the record using order\_amount.@3.tax\_amount.

You can identify the type used for each iteration or an element using the function resolveChoice. For more information about this function, see [Special functions](https://help.qlik.com/talend/en-US/data-shaping-language-reference-guide/8.0/special-functions).

In this example, you can use the following query to get the data type used for the order\_amount element in each item in the orders array:

```
FROM orders
SELECT {
    order_id,
    amount = resolveChoice(order_amount)
}
```

This query returns the following result:

```
[
    {
        "order_id": "abc-12345",
        "amount_type": 0
    },
    {
        "order_id": "def-67890",
        "amount_type": 2
    },
    {
        "order_id": "ghi-54321",
        "amount_type": 1
    },
    {
        "order_id": "jkl-09876",
        "amount_type": 3
    }
]
```

You can then write a query to harmonize your data and return a single data type for the order\_amount element. For example, you can use the query below to:

* Assign a type to each alternative index using a switch expression and the resolveChoice function.
* Convert the string and integer values to decimals using the toDecimal function.
* Calculate the total amount for each record by adding tax\_amount to items\_total.

```
FROM orders
LET $amount_type = 
    SWITCH (resolveChoice(order_amount)) { 
		CASE 0: 'INT' 
		CASE 1: 'DECIMAL' 
		CASE 2: 'STRING' 
		CASE 3: 'RECORD' 
	}
SELECT {
    order_id,
    amount = 
        IF ($amount_type = "STRING" OR $amount_type = "INT") 
            toDecimal(order_amount)
        ELSEIF ($amount_type = "RECORD")
            order_amount.@3.items_total + order_amount.@3.tax_amount
        ELSE order_amount
}
```

With this query, each iteration of the order\_amount is converted to a decimal value, regardless of the input type. It returns the following result:

```
[
    {
        "order_id": "abc-12345",
        "amount": 250
    },
    {
        "order_id": "def-67890",
        "amount": 299.99
    },
    {
        "order_id": "ghi-54321",
        "amount": 435.95
    },
    {
        "order_id": "jkl-09876",
        "amount": 407.88
    }
]
```