SQL Server table for json

Question

I am looking to pull out a few columns (Col1 and 2) of a table and put in JSON format, and also write some hard-coded JSON in each node, like this.

{"col1": "xxxx", "col2": "xxxx", "hardcodedString": "xxxx", "hardcodedString": "xxxx", "hardcodedString": "xxxx", "hardcodedString": "xxxx", " hardcodedString ":" xxxx "},

I found the following git script, it creates an SP that should generate JSON, but when I execute as needed, I get "Commands Completed Succesfully"

Any ideas that come to the conclusion, or is it really the best way to get my JSON?

create procedure [dbo].[GetJSON] ( @schema_name varchar(50), @table_name varchar(50), @registries_per_request smallint = null ) as begin if ( ( select count(*) from information_schema.tables where table_schema = @schema_name and table_name = @table_name ) > 0 ) begin declare @json varchar(max), @line varchar(max), @columns varchar(max), @sql nvarchar(max), @columnNavigator varchar(50), @counter tinyint, @size varchar(10) if (@registries_per_request is null) begin set @size = '' end else begin set @size = 'top ' + convert(varchar, @registries_per_request) end set @columns = '{' declare schemaCursor cursor for select column_name from information_schema.columns where table_schema = @schema_name and table_name = @table_name open schemaCursor fetch next from schemaCursor into @columnNavigator select @counter = count(*) from information_schema.columns where table_schema = @schema_name and table_name = @table_name while @@fetch_status = 0 begin set @columns = @columns + '''''' + @columnNavigator + ''''':'''''' + convert(varchar, ' + @columnNavigator + ') + ''''''' set @counter = @counter - 1 if ( 0 != @counter ) begin set @columns = @columns + ',' end fetch next from schemaCursor into @columnNavigator end set @columns = @columns + '}' close schemaCursor deallocate schemaCursor set @json = '[' set @sql = 'select ' + @size + '''' + @columns + ''' as json into tmpJsonTable from [' + @schema_name + '].[' + @table_name + ']' exec sp_sqlexec @sql select @counter = count(*) from tmpJsonTable declare tmpCur cursor for select * from tmpJsonTable open tmpCur fetch next from tmpCur into @line while @@fetch_status = 0 begin set @counter = @counter - 1 set @json = @json + @line if ( 0 != @counter ) begin set @json = @json + ',' end fetch next from tmpCur into @line end set @json = @json + ']' close tmpCur deallocate tmpCur drop table tmpJsonTable select @json as json end end 

Answer 1

I would not recommend this, there are much better ways to do this at the application level, but the following avoid loops and are much less verbose than your current method:

 CREATE PROCEDURE dbo.GetJSON @ObjectName VARCHAR(255), @registries_per_request smallint = null AS BEGIN IF OBJECT_ID(@ObjectName) IS NULL BEGIN SELECT Json = ''; RETURN END; DECLARE @Top NVARCHAR(20) = CASE WHEN @registries_per_request IS NOT NULL THEN 'TOP (' + CAST(@registries_per_request AS NVARCHAR) + ') ' ELSE '' END; DECLARE @SQL NVARCHAR(MAX) = N'SELECT ' + @Top + '* INTO ##T ' + 'FROM ' + @ObjectName; EXECUTE SP_EXECUTESQL @SQL; DECLARE @X NVARCHAR(MAX) = '[' + (SELECT * FROM ##T FOR XML PATH('')) + ']'; SELECT @X = REPLACE(@X, '<' + Name + '>', CASE WHEN ROW_NUMBER() OVER(ORDER BY Column_ID) = 1 THEN '{' ELSE '' END + Name + ':'), @X = REPLACE(@X, '</' + Name + '>', ','), @X = REPLACE(@X, ',{', '}, {'), @X = REPLACE(@X, ',]', '}]') FROM sys.columns WHERE [Object_ID] = OBJECT_ID(@ObjectName) ORDER BY Column_ID; DROP TABLE ##T; SELECT Json = @X; END 

NB I changed the object name of your part (@schema and @table) to just accept the full name of the object.

SQL script example

The idea is to basically use the XML extension in SQL Server to turn the table into XML, and then just replace the start tags with {ColumnName: and the end tags {ColumnName: Then, two more replacements are required to stop adding the closing parenthesis to the last column of each row and remove the final , from the JSON row.

Answer 2

Use from magic words For JSON

example:

  SELECT name, surname FROM emp FOR JSON AUTO 

result:

 [{"name": "John"}, {"name": "Jane", "surname": "Doe"}] 

more information in:

https://docs.microsoft.com/en-us/sql/relational-databases/json/format-query-results-as-json-with-for-json-sql-server?view=sql-server-2017&viewFallbackFrom= SQL Server 2014

Answer 3

In ruby ​​using mysql2 gem and json

 require 'mysql2' require 'json' client = Mysql2::Client.new(:host => "localhost", :username => "user", :password => "your_pass", :database => "db_name") results = client.query("SELECT col1, col2 FROM table LIMIT 3").to_a results.each do |res| res.merge!({:hardcoded => "xxxx", :hardcoded2 => "xxxxy"}) end puts JSON.generate results