3

Using Azure SQLServer I'm trying to convert only one column to json format. The data is in a nvarchar field. Using For Json Auto will convert all fields and all rows. What I need is just to convert only one column.

By converting to json what I mean is to be clickable to see it's data a new window inside SSMS.

Let's say the table (Logs) has 2 columns: LogId and LogData. LogData is nvarchar(max) and contains json data.

What I need is to query the table and have a clickable logData column.

EzLo
  • 13,780
  • 10
  • 33
  • 38
Kamran
  • 1,258
  • 1
  • 16
  • 28
  • `FOR JSON` will just show the results of the columns and rows you are selecting. You probably have a `*` on the column list. Add a new column of JSON type and do an UPDATE to set it's value, then drop the old column. – EzLo Feb 05 '19 at 10:22
  • Thanks @EzLo FOR JSON gives me just one json for all columns and rows. I'm looking for a way to just somehow convert one column. – Kamran Feb 05 '19 at 10:32
  • 1
    Sample data and desired results can really help understand the question. Json is not a type, it's a serialization format - `nvarchar` can contain whatever string you want, formatted as Json or not. Please [edit] your question to include the relevant table DDL + some DML for sample data as well as desired results. – Zohar Peled Feb 05 '19 at 10:41
  • thanks @ZoharPeled. I added more info – Kamran Feb 05 '19 at 10:51
  • What do you mean by `clickable`? where do you run the query? – Zohar Peled Feb 05 '19 at 11:13
  • @ZoharPeled, this is for SSMS. when we have json in the result grid which is a json instead of simple text it become a link and by clicking on it it will open a new window and display the json in that window. The main goal that I want to achieve here is to not copy and paste the json column into notepad to check it. – Kamran Feb 06 '19 at 12:19

1 Answers1

10

You can try like following to get only one column as JSON

select o.*,
 (select YourColumnForJson  
    from YourTable i 
        where o.Identifer=i.Identifer for json auto) as JsonColumn 
 from YourTable o
PSK
  • 17,547
  • 5
  • 32
  • 43