How to get result in json format?

you can use the FOR JSON clause in Microsoft SQL Server or a similar clause in other databases that support JSON output.

Here's an example using the FOR JSON AUTO clause in Microsoft SQL Server:

SELECT column1, column2, ...
FROM table_name
WHERE condition
FOR JSON AUTO;

This query will return the result set in JSON format. The FOR JSON AUTO clause automatically generates a JSON representation of the result set based on the table structure.

Here's a simple example:

SELECT FirstName, LastName, Age
FROM Persons
WHERE Age > 25
FOR JSON AUTO;

This would return a JSON array of objects representing the selected data:

[
  {"FirstName": "John", "LastName": "Doe", "Age": 30},
  {"FirstName": "Jane", "LastName": "Smith", "Age": 28},
  ...
]

Note that the exact syntax may vary between different database systems, and some databases might not support JSON output directly. If you're using a different database, please refer to its documentation for the specific syntax for retrieving JSON-formatted results.

Hello Jaya,

Getting each row value in json format or want to export data from SQL Server as JSON, you need to add FOR JSON clause to a SELECT statement.

Below is a sample query that uses PATH mode FOR JSON clause. 

SELECT * FROM table FOR JSON PATH;

Also you can use JSON_EXTRACT function that allows you to extract a value from a JSON string.

Here is an example of how to use the JSON_EXTRACT function in a SELECT statement:

SELECT JSON_EXTRACT(json_column, '$.key1.key2')
FROM table
WHERE json_column IS NOT NULL;

Hope this helps!

Hello Jaya Prakash,

Please refer this one will help you,

DECLARE @json NVARCHAR(MAX) = '{
"EmpDetails": [
    {
        "Id" : 1,
		"Name" : "Name-1"
    },
    {
       "Id" : 2,
	   "Name" : "Name-2"
    }
]
}'


SELECT  
    JSON_VALUE(list_value.value, '$.Id') AS [Id],
	JSON_VALUE(list_value.value, '$.Name') AS [Name]    
FROM OPENJSON(@json, '$') AS empData
cross apply openjson(empData.value, '$') as list_value

Result :