July 10, 2018 at 2:20 pm
Hey Guys,
I have this example below:
CREATE TABLE #Test
(
ID int,
Personname varchar(16),
JsonColumn varchar(256)
)
insert into #Test(ID,Personname,JsonColumn)
values(1,'bob','[{"TableName:Orders,"ID":10},{"TableName":"PurchaseOrders","ID":15}]')
What i am trying to accomplish is of course get the ID, the Person name, BUT, trying to only bring back if TableName = Orders, if the table name equals orders, then get the ID, which is 10, so the results I would like to show is:
ID PersonName Order
1 Bob 10
hope this all makes sense, i am googleing around but not sure how to search for it, and even how to do query json 🙁
thanks in advance
July 10, 2018 at 2:36 pm
Siten0308 - Tuesday, July 10, 2018 2:20 PMHey Guys,I have this example below:
CREATE TABLE #Test
(
ID int,
Personname varchar(16),
JsonColumn varchar(256)
)insert into #Test(ID,Personname,JsonColumn)
values(1,'bob','[{"TableName:Orders,"ID":10},{"TableName":"PurchaseOrders","ID":15}]')What i am trying to accomplish is of course get the ID, the Person name, BUT, trying to only bring back if TableName = Orders, if the table name equals orders, then get the ID, which is 10, so the results I would like to show is:
ID PersonName Order
1 Bob 10hope this all makes sense, i am googleing around but not sure how to search for it, and even how to do query json 🙁
thanks in advance
First, your JSON string is incorrect. After correcting the JSON string I got the following:
CREATE TABLE #Test
(
ID int,
Personname varchar(16),
JsonColumn nvarchar(256)
);
insert into #Test(ID,Personname,JsonColumn)
values(1,'bob','[{"TableName":"Orders","ID":10},{"TableName":"PurchaseOrders","ID":15}]');
SELECT * FROM [#Test] AS [t];
SELECT
[t].[ID]
, [t].[Personname]
, [ca1].[Id]
FROM
[#Test] AS [t]
CROSS APPLY(SELECT * FROM OPENJSON([t].[JsonColumn]) WITH ([TableName] VARCHAR(16) '$.TableName', [Id] INT '$.ID'))AS ca1(TableName,Id)
WHERE
[ca1].[TableName] = 'Orders';
GO
DROP TABLE [#Test];
GO
July 10, 2018 at 3:09 pm
awesome big thanks Lynn,
one last thing, i am really sorry, is there a way to do this:
ID PersonName Order Purchase order
1 Bob 10 15
July 10, 2018 at 3:43 pm
Siten0308 - Tuesday, July 10, 2018 3:09 PMawesome big thanks Lynn,one last thing, i am really sorry, is there a way to do this:
ID PersonName Order Purchase order
1 Bob 10 15
CREATE TABLE #Test
(
ID int,
Personname varchar(16),
JsonColumn nvarchar(256)
);
insert into #Test(ID,Personname,JsonColumn)
values(1,'bob','[{"TableName":"Orders","ID":10},{"TableName":"PurchaseOrders","ID":15}]');
SELECT * FROM [#Test] AS [t];
SELECT
[t].[ID]
, [t].[Personname]
, [Order] = [ca1].[Id]
, [PurchaseOrder] = [ca2].[Id]
FROM
[#Test] AS [t]
CROSS APPLY(SELECT * FROM OPENJSON([t].[JsonColumn]) WITH ([TableName] VARCHAR(16) '$.TableName', [Id] INT '$.ID'))AS ca1(TableName,Id)
CROSS APPLY(SELECT * FROM OPENJSON([t].[JsonColumn]) WITH ([TableName] VARCHAR(16) '$.TableName', [Id] INT '$.ID'))AS ca2(TableName,Id)
WHERE
[ca1].[TableName] = 'Orders'
AND [ca2].[TableName] = 'PurchaseOrders';
GO
DROP TABLE [#Test];
GO
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply