For Auto JSON with where condition

Question

Hi All,

I need to create a view to export the data in SON format and i need to limit the data by adding where condition. Please see the view and sample select statements.

Create view abc
as
select emp,ename,sal from emp for auto json

running t-sql queries

Select * from abc where emp=1
or
Select * from abc where ename='abc'

Answer 1

Are you going through the hard way to filter your view?
Try these sample (it is possible but not efficient)
Drop table if exists emp;
create table emp (emp int,ename varchar(10),sal decimal(6,2))
insert into emp values(1,'name1',1000),(2,'abc',2000),(3,'def',3000)

go  
Drop view  if  exists abc;  
go  
Create view abc  
(jsonCol)  
as  
select emp,ename,sal   
from emp for json auto  
  
go  
--example 1  
;with mycte as (  
SELECT *   
FROM OPENJSON(CONVERT(NVARCHAR(MAX),(select jsonCol from abc)))  
WITH  
    (  
	emp int,  
    ename VARCHAR(200) '$.ename',  
    sal decimal(6,2) '$.sal'  
    )  
	 WHERE emp = '1' )  
  
 Select * from mycte  for json auto  
  
--example 2  
;with mycte2 as (  
SELECT *   
FROM OPENJSON(CONVERT(NVARCHAR(MAX),(select * from abc)))  
   WITH  
    (  
	emp int,  
    ename VARCHAR(200) '$.ename',  
    sal decimal(6,2) '$.sal'  
    )  
    WHERE ename = 'abc'  
	)  
  
 Select * from mycte2  for json auto  

Answer 2

you cannot pass parameter to a view in SQL Server. You can use a stored procedure to pass parameter instead.

Answer 3

I am not passing any parameters. I am only running the select statement by a where condition.

Answer 4

Create view abc
as
select emp,ename,sal from emp
where emp=1
for json auto

Answer 5

I do not want to alter the View for each where condition.