# Querying and Manipulating JSON data

<table><thead><tr><th width="89" align="center">S.No.</th><th align="center">State</th><th align="center">Capital</th><th align="center">RTO Code</th></tr></thead><tbody><tr><td align="center">1</td><td align="center">Telangana</td><td align="center">Hyderabad</td><td align="center">TG</td></tr><tr><td align="center">2</td><td align="center">Karnataka</td><td align="center">Bengaluru</td><td align="center">KA</td></tr><tr><td align="center">3</td><td align="center">Kerala</td><td align="center">Thiruvananthapuram</td><td align="center">KR</td></tr><tr><td align="center">4</td><td align="center">Madhya Pradesh</td><td align="center">Bhopal</td><td align="center">MP</td></tr><tr><td align="center">5</td><td align="center">Maharashtra</td><td align="center">Mumbai</td><td align="center">MH</td></tr><tr><td align="center">6</td><td align="center">Gujarat</td><td align="center">Gandhi Nagar</td><td align="center">GJ</td></tr></tbody></table>

### Getting results of queries in JSON

Adding the below line at the end of the query will return the result in JSON format.\
`FOR JSON PATH`

We can also have a root element by adding `ROOT('root_name')` after `FOR JSON PATH`

```sql
SELECT * FROM CapitalOfIndianStates
FOR JSON PATH, ROOT('StateCapitals')
```

{% hint style="info" %}
Using this SQL statement will help us directly returning JSON data at the database level without the need of serializing the data at the application level.
{% endhint %}

### Reading JSON in tabular form&#x20;

**`OPENJSON()`**

This function is used to parse the entire JSON object in tabular form.\
Let's understand with an example.

Consider we have a JSON object which we have saved into a variable.&#x20;

```sql
DECLARE @Info NVARCHAR(MAX);
SET @Info = N'[
    {
        "Id": 1,
        "FirstName": "John", 
        "LastName": "Doe",
        "Gender": 'M',
        "Age": 22        
    },
    {
        "Id": 2,
        "FirstName": "Jane", 
        "LastName": "Doe",
        "Gender": 'F',
        "Age": 22        
    },
]';
```

To display these JSON object in tabular form (rows/columns) we pass this variable as an argument to **`OPENJSON()`**. After that, we need to tell the function what datatype will go with each property. We do this by specifying each column name with its corresponding data type inside the`WITH`clause.

```sql
SELECT * FROM OPENJSON(@Info)
WITH (
    Id INT 'strict S.Id'
    FirstName VARCHAR(50) '$.FirstName',
    LastName VARCHAR(50) '$.LastName',
    Gender CHAR(2) '$.Gender',
    Age INT '$.Age'
);
```

> Note: **`strict`** keyword we used in our code is to speecify that **`Id`** must exist in the JSON object.

{% embed url="https://app.pluralsight.com/guides/querying-and-manipulating-json-data-with-t-sql" %}
