Convert two columns into key-value json object?

Question

I am looking for a query with the native functions of SQL Server 2012 to extract the JSON from the column Value, and dynamically create columns, and I want to do this for different columns I have multiple record ID Data Other 122 {"URL":"www.url.com","Icon":"some_icon","Facebook":"Fa_url"} Test123 222 {"URL":"www.url.com","Icon":"some_icon", Facebook":"Fa_url"} test123 I need to this formate id URL Icon Facebook Google Other 122 www.abc.com Some_icon Facebook_url abc test123 222 www.abc.com Some_icon Facebook_url abc test123

Bryian Tan · Answer

You can use the parseJSON function here https://www.red-gate.com/simple-talk/sql/t-sql-programming/consuming-json-strings-in-sql-server/ Example: DECLARE @ temp TABLE ( Id INT IDENTITY(1,1), [Data] VARCHAR (500), OTHER VARCHAR (50) ) INSERT INTO @ temp SELECT '{"URL":"www.url.com","Icon":"some_icon","Facebook":"Fa_url"}' , 'Test123' UNION SELECT '{"URL":"www.url2.com","Icon":"some_icon2", "Facebook":"Fa_url2"}' , 'Test456' SELECT t.id, A.*, t.OTHER FROM @ temp t CROSS APPLY ( Select max ( case when name = 'URL' then convert ( Varchar (500),StringValue) else '' end ) as URL, max ( case when name = 'Icon' then convert ( Varchar (500),StringValue) else '' end ) as Icon, max ( case when name = 'Facebook' then convert ( Varchar (500),StringValue) else '' end ) as Facebook from dbo.parseJSON( t.Data) ) A Output: id URL Icon Facebook OTHER 1 www.url.com some_icon Fa_url Test123 2 www.url2.com some_icon2 Fa_url2 Test456

Shrimant Telgave · Answer

I hope the following code will help you to achieved your functionality. Solution-1: Select * from parseJSON('{ "Person" : { "firstName" : "Ram" , "lastName" : "S" , "age" : 25, "Address" : { "streetAddress" : "21 2nd Street" , "city" : "Pune" , "state" : "MH" , "postalCode" : "400545" }, "PhoneNumbers" : { "home" : "22222222" , "fax" : "888 888 888 " } } } ') Solution-2: DECLARE @json NVARCHAR( MAX ) SET @json = N'[ { "Order" : { "Number" : "SO43659" , "Date" : "2011-05-31T00:00:00" }, "AccountNumber" : "AW29825" , "Item" : { "Price" :2024.9940, "Quantity" :1 } }, { "Order" : { "Number" : "SO43661" , "Date" : "2011-06-01T00:00:00" }, "AccountNumber" : "AW73565" , "Item" : { "Price" :2024.9940, "Quantity" :3 } } ]' SELECT * FROM OPENJSON ( @json ) WITH ( Number varchar (200) '$.Order.Number' , Date datetime '$.Order.Date' , Customer varchar (200) '$.AccountNumber' , Quantity int '$.Item.Quantity' ) Output: Number Date Customer Quantity SO43659 2011-05-31T00:00:00 AW29825 1 SO43661 2011-06-01T00:00:00 AW73565 3 only you need to change the column name in the above query. I hope this will help you to resolved your problem.

arvind baldaniya · Answer

thank u shrimant OPENJSON is not working for my site because My SQL server( Microsoft SQL Server 2012 - 11.0.5058.0 (X64)) lower version

arvind baldaniya · Answer

sorry Not working i need to dynamic DataTable

arvind baldaniya · Answer

Thank u Gjanan i am using parseJSON is perfect working... select max(case when name='Rate' then convert(Varchar(50),StringValue) else '' end) as [Rate], max(case when name='PRP' then convert(Varchar(50),StringValue) else '' end) as [PRP], max(case when name='QRS' then convert(Varchar(50),StringValue) else '' end) as [QRS], max(case when name='QTQ' then convert(Varchar(50),StringValue) else '' end) as [QT], max(case when name='Qtc' then convert(Varchar(50),StringValue) else '' end) as [QTC], max(case when name='RR' then convert(Varchar(50),StringValue) else '' end) as [RR] from parseJSON('[{"Rate":"1","PRP":"2","QRS":"3","QTQ":"4","Qtc":"5","RR":"TER"},{"Rate":"1","PRP":"2","QRS":"3","QTQ":"4","Qtc":"5","RR":"TER"}]') where ValueType = 'string' OR ValueType = 'boolean' group by parent_ID but i have lots of record, above record is static...i need to dynamic record check out attached file

Gajanan Chavhan · Answer

Any specific reason Openjson is not working for you. Also I found similar links : 1) https://www.red-gate.com/simple-talk/blogs/sql-server-json-to-table-and-table-to-json/ 2) https://stackoverflow.com/questions/33520910/transform-json-data-to-separate-columns

arvind baldaniya · Answer

using System; using System.Collections.Generic; using System.Linq; using System.Web; using System.Web.Mvc; using MVCDemo.Models; using System.Data; using System.Data.Entity; using System.IO; namespace MVCDemo.Controllers { public class TestController : Controller { InterviewEntities db = new InterviewEntities(); public ActionResult Index() { ViewBag.detail = db.Regs.ToList(); return View(); } public ActionResult Add() { ViewBag.country = db.Countries.ToList(); ViewBag.state = db.States.ToList(); ViewBag.detail = db.Regs.ToList(); return View(); } [HttpPost] public ActionResult Add(Reg pro, HttpPostedFileBase fileUpload) { pro.CityId = Convert.ToInt32(Request.Form["cityid"]); db.Regs.Add(pro); db.SaveChanges(); if (fileUpload.ContentLength > 0 && fileUpload != null) { string dir_path = Server.MapPath("~/Images/" + pro.Id); if (fileUpload != null && fileUpload.ContentLength > 0) { if (!Directory.Exists(dir_path)) Directory.CreateDirectory(dir_path); string only_name = Path.GetFileNameWithoutExtension(fileUpload.FileName); string filename = only_name + Path.GetExtension(fileUpload.FileName); string pathtosave = Path.Combine(dir_path, filename); fileUpload.SaveAs(pathtosave); pro.Fileupload = "/Images/" + pro.Id + "/" + filename; db.Entry(pro).State = System.Data.Entity.EntityState.Modified; db.SaveChanges(); } } ViewBag.country = db.Countries.ToList(); ViewBag.state = db.States.ToList(); ViewBag.detail = db.Regs.ToList(); return View(); } public ActionResult Edit() { string id = Request.QueryString["eid"]; int idd = Convert.ToInt32(id); Reg obj = db.Regs.Find(idd); return View(obj); } [HttpPost] public ActionResult Edit(Reg Pro) { Pro.CityId = Convert.ToInt32(Request.Form["cityid"]); db.Entry(Pro).State = System.Data.Entity.EntityState.Modified; ViewBag.country = db.Countries.ToList(); ViewBag.state = db.States.ToList(); ViewBag.detail = db.Regs.ToList(); db.SaveChanges(); return View("Index"); } public ActionResult Delete(int Id) { int IDD = Convert.ToInt32(Id); Reg Reg = db.Regs.Find(IDD); db.Regs.Remove(Reg); db.SaveChanges(); return View(); } public ActionResult getstate(string id) { int countryid = Convert.ToInt32(id); List list_state = db.States.Where(x => x.CountryId == countryid).ToList(); List liststate = new List (); foreach (var item in list_state) { returnstate obj = new returnstate(); obj.Id = item.Id; obj.StateName = item.Name; liststate.Add(obj); } return Json(new { state = liststate }, JsonRequestBehavior.AllowGet); } public ActionResult getcity(string id) { int stateid = Convert.ToInt32(id); List list_city = db.Cities.Where(x => x.StateId == stateid).ToList(); List listcity = new List (); foreach (var item in list_city) { returncity obj = new returncity(); obj.Id = item.Id; obj.cityname = item.City1; listcity.Add(obj); } return Json(new { city = listcity }, JsonRequestBehavior.AllowGet); } public JsonResult GetAge(string input) { //DateTime dt_input = DateTime.ParseExact(input, "dd-MM-YYYY", CultureInfo.InvariantCulture); DateTime dt_input = Convert.ToDateTime(input); DateTime today = DateTime.Today; int months = today.Month - dt_input.Month; int years = today.Year - dt_input.Year; if (today.Day { months--; } if (months { years--; months += 12; } int days = (today - dt_input.AddMonths((years * 12) + months)).Days; string rerutnstring = years + ""; return new JsonResult() { JsonRequestBehavior = JsonRequestBehavior.AllowGet, Data = new { title = rerutnstring } }; } public class returncity { public int Id { get; set; } public string cityname { get; set; } } public class returnstate { public int Id { get; set; } public string StateName { get; set; } } } } @using MVCDemo.Models @model MVCDemo.Models.Reg @{ ViewBag.Title = "Add"; } @using (Html.BeginForm("Add", "Test", FormMethod.Post, new { @id = "Interviewform", enctype = "multipart/form-data" })) { Fname @Html.TextBoxFor(x =>x.Name) Lname @Html.TextBoxFor(x =>x.LName) EmailId @Html.TextBoxFor(x =>x.EmailId) @* DOB @Html.TextBoxFor(x =>x.DOB)*@ DBO Age Country @Html.DropDownList("Country", new SelectList(ViewBag.country, "Id", "Name"), new { @onchange = "fillState()" }); State City Uplodad } @* *@ document.onload = fillState(); function fillState() { var countryid = $("#Country").val(); $("#Country").val(); $.ajax({ url: "/Test/getstate", type: "post", data: { 'id': countryid }, success: function (data) { $("#stateid").text(''); for (i = 0; i $("#stateid").append(' ' + data.state[i].StateName + ' '); } fillcity(); } }); } function fillcity() { var stateid = $("#stateid").val(); $.ajax({ url: "/Test/getcity", type: "post", data: { 'id': stateid }, success: function (data) { $("#cityid").text(''); for (i = 0; i $("#cityid").append(' ' + data.city[i].cityname + ' '); } } }); } $(function () { $("#Interviewform").find("#datepicker").datepicker({ onSelect: function (selected) { var dt = new Date(selected); dt.setDate(dt.getDate() + 1); $.ajax({ type: "GET", contentType: "application/json; charset=utf-8", global: false, url: "/Test/GetAge", data: { 'input': $("#datepicker").val() }, dataType: "json", success: function (data) { $("#row_age_1").val(data.title); }, error: function (errormsg) { console.log("Error:" + errormsg.responseText) } }); } }); }); @using MVCDemo.Models; @{ ViewBag.Title = "Index"; } ID Fname City @foreach (var i in (IEnumerable )@ViewBag.detail) { @i.Id @i.Name @i.City.City1 Edit | Delete } $(document).ready(function () { $('#MyTable').DataTable(); }); @using MVCDemo.Models @model MVCDemo.Models.Reg @{ ViewBag.Title = "Edit"; } @using (Html.BeginForm("Edit", "Test", FormMethod.Post, new { @class = "form-horizontal" })) { @Html.HiddenFor(x => x.Id) Fname @Html.TextBoxFor(x => x.Name) Lname @Html.TextBoxFor(x => x.ProductAdd) EmailId @Html.TextBoxFor(x => x.ProductName) Ok }

Gajanan Chavhan · Answer

Below Link might help you: https://docs.microsoft.com/en-us/sql/relational-databases/json/convert-json-data-to-rows-and-columns-with-openjson-sql-server?view=sql-server-2017

ID	Data	Other
122	{"URL":"www.url.com","Icon":"some_icon","Facebook":"Fa_url"}	Test123
222	{"URL":"www.url.com","Icon":"some_icon", Facebook":"Fa_url"}	test123

id	URL	Icon	Facebook	Google	Other
122	www.abc.com	Some_icon	Facebook_url	abc	test123
222	www.abc.com	Some_icon	Facebook_url	abc	test123

Convert two columns into key-value json object?

Answers (8)