Help parsing a fields value to create 3 new columns

Question

Post reply

Help parsing a fields value to create 3 new columns

Jeffs1977

SSC-Addicted

Points: 487
More actions
June 22, 2022 at 2:53 pm

#4053343

I need some help with parsing out the field [JObject].
In the application, there is a setting by user to "View", "Edit" and "Admin". The data is stored in this field as "True" or "False". I need to look at this field and create 3 new columns in my SQL Output, so I can render it back in an SSRS report. Can someone help me parse this for the 3 values and create them into the 3 new columns (View,Edit and Admin) based on the True or False values.

Viewing 9 posts - 1 through 8 (of 8 total)

You must be logged in to reply to this topic. Login to reply

Phil Parkin SSC Guru Points: 246965 More actions · Answer 1

Can you confirm the exact format of JObject? It looks like

{"View"true,"Edit"true,"Admin"true}

Is that right?

The absence of evidence is not evidence of absence
- Martin Rees
The absence of consumable DDL, sample data and desired results is, however, evidence of the absence of my response
- Phil Parkin

Thom A SSC Guru Points: 99130 More actions · Answer 2

As it's JSON, treat is as JSON.

I can't test this, as copying text from an image isn't easy (please use DDL and DML statements in the future), but you likely simply want:

SELECT {Columns you need from YourTable},
       JO.[View],
       JO.Edit,
       JO.Admin
FROM dbo.YourTable YT
     CROSS APPLY OPENJSON(YT.JObject)
                 WITH ([View] varchar(5),
                       Edit varchar(5),
                       Admin varchar(5)) JO;

Thom~

Excuse my typos and sometimes awful grammar. My fingers work faster than my brain does.
Larnu.uk

Jeffs1977 SSC-Addicted Points: 487 More actions · Answer 3

Jeffs1977

SSC-Addicted

Points: 487

June 22, 2022 at 3:12 pm

#4053350

Yes and those True's can be a False as well.

Thom A SSC Guru Points: 99130 More actions · Answer 4

Phil Parkin wrote:

Can you confirm the exact format of JObject? It looks like
{"View"true,"Edit"true,"Admin"true}
Is that right?

It's real fuzzy, but the colons are there. Another reason images of data are bad, Jeffs.

Thom~

Excuse my typos and sometimes awful grammar. My fingers work faster than my brain does.
Larnu.uk

Steve Collins SSCrazy Points: 2159 More actions · Answer 5

JObject seems to be a JSON object so you could use JSON_VALUE to select the fields

declare @json           nvarchar(max)=N'{"View":true, "Edit":true, "Amin":false}'

/* use cast to convert boolean to a sql type */
select json_value(@json, N'$.View'), 
       cast(json_value(@json, N'$.Edit') as bit) edit_bit, 
       cast(json_value(@json, N'$.Amin') as bit) admin_bit;

Aus dem Paradies, das Cantor uns geschaffen, soll uns niemand vertreiben können

Phil Parkin SSC Guru Points: 246965 More actions · Answer 6

Thom A wrote:

Phil Parkin wrote:
Can you confirm the exact format of JObject? It looks like
{"View"true,"Edit"true,"Admin"true}
Is that right?
It's real fuzzy, but the colons are there. Another reason images of data are bad, Jeffs.

Blimey. From now on, you shall be 'Thom the Eagle'!

The absence of evidence is not evidence of absence
- Martin Rees
The absence of consumable DDL, sample data and desired results is, however, evidence of the absence of my response
- Phil Parkin

Jeffs1977 SSC-Addicted Points: 487 More actions · Answer 7

Thom,

My apologies, the SQL I used is

select *

from [ApplicationCenter].[AccessControlLists]

WHERE NodeId = 60

I tried your SQL and got an error.

Msg 319, Level 15, State 1, Line 21

Incorrect syntax near the keyword 'with'. If this statement is a common table expression, an xmlnamespaces clause or a change tracking context clause, the previous statement must be terminated with a semicolon.

Completion time: 2022-06-22T10:16:02.4635221-05:00

SELECT JO.*,

JO.[View],

JO.Edit,

JO.Admin

FROM [ApplicationCenter].[AccessControlLists] YT

CROSS APPLY OPENJSON(YT.JObject)

WITH ([View] varchar(5),

Edit varchar(5),

Admin varchar(5)) JO;

Thom A SSC Guru Points: 99130 More actions · Answer 8

Jeffs1977 wrote:

I tried your SQL and got an error.
Msg 319, Level 15, State 1, Line 21 Incorrect syntax near the keyword 'with'. If this statement is a common table expression, an xmlnamespaces clause or a change tracking context clause, the previous statement must be terminated with a semicolon.

You would only get that error if you are on a version that doesn't support JSON, but you've posted in the 2019 forum here, so it does. Are you actually not using SQL Server 2019? What version are you therefore using? JSON support was added back in SQL Server 2016, so it's in all mainstream supported version of SQL Server.

If you don't have JSON support, then trying to consume JSON in SQL Server is not going to be "fun"; I'd suggest using something else like XML (not ideal), storing it in a normalised format (better), or even upgrading (better).

Thom~

Excuse my typos and sometimes awful grammar. My fingers work faster than my brain does.
Larnu.uk