Help parsing a fields value to create 3 new columns

  • 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.

    Snipaste_2022-06-22_09-48-52

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

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

    Is that right?

    If you haven't even tried to resolve your issue, please don't expect the hard-working volunteers here to waste their time providing links to answers which you could easily have found yourself.

  • 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

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

  • 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

  • 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

  • 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'!

    If you haven't even tried to resolve your issue, please don't expect the hard-working volunteers here to waste their time providing links to answers which you could easily have found yourself.

  • 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;

     

  • 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

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

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