Convert two columns into key-value json object?

arvind baldaniya

8y3.7kSQL

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

8 Replies

Know the answer? Post it — somebody with the same question will find it here.

Bryian Tan8y ago
Accepted 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
arvind baldaniya8y ago
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

Shrimant Telgave8y ago

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 baldaniya8y ago
sorry Not working i need to dynamic DataTable
arvind baldaniya8y ago
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 Chavhan8y ago
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 baldaniya8y ago
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 < dt_input.Day)
{
months--;
}
if (months < 0)
{
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

}
@**@
@using MVCDemo.Models;
@{
ViewBag.Title = "Index";
}

@foreach (var i in (IEnumerable)@ViewBag.detail)
{

}

ID Fname City

@i.Id @i.Name @i.City.City1
Edit|Delete

@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)

}
Gajanan Chavhan8y ago
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	Fname	City

@i.Id	@i.Name	@i.City.City1	Edit\|Delete