SQL to add quotes to a attribute inside JSON

Question

I have a table (DE in SFMC) with the following schema

Incoming_data

Id -- Text(40)
Data_Json -- Text(4000)

The field Data_Json contains data in JSON format. Sample data shown below

ID                      DATA_JSON
------------------      ----------------------------------------------------------------------------------
123456                   {'EMPLOYEE_ID':'12345678','EMPLOYEE_DEPT':'SALES', 'EMPLOYEE_START_DATE':'2022-08-01','EMPLOYEE_HIRE_DATE':'2022-07-15','EMPLOYEE_NAME':'TEST','EMPLOYEE_DEPT_NO':1,'EMPLOYEE_DEPT_CAT':'SL_LOB', 'EMPLOYEE_EMAIL_ADD':'ABCTEST@DOESNOTEXIST.ca','EMPLOYEE_TYPE':'F'}
 
222333                    {'EMPLOYEE_ID':'13578912','EMPLOYEE_START_DATE':'2022-09-01','EMPLOYEE_HIRE_DATE':'2022-08-15','EMPLOYEE_NAME':'TEST2','EMPLOYEE_EMAIL_ADD':'TEST2@DOESNOTEXIST.ca','EMPLOYEE_TYPE':'C'}
 
444555                    {'EMPLOYEE_ID':'23134587','EMPLOYEE_START_DATE':'2022-11-15','EMPLOYEE_HIRE_DATE':'2022-10-15','EMPLOYEE_NAME':'TEST3','EMPLOYEE_EMAIL_ADD':'TEST3@DOESNOTEXIST.ca','EMPLOYEE_TYPE':'P'}
 
666777                    {'EMPLOYEE_ID':'44555678','EMPLOYEE_DEPT':'MARKET', 'EMPLOYEE_START_DATE':'2022-06-01','EMPLOYEE_HIRE_DATE':'2022-05-15','EMPLOYEE_NAME':'TEST4','EMPLOYEE_DEPT_NO':3,'EMPLOYEE_DEPT_CAT':'PT_MAKT', 'EMPLOYEE_EMAIL_ADD':'TEST4@DOESNOTEXIST.ca','EMPLOYEE_TYPE':'F'}

Using SQL, How to modify the EMPLOYEE_DEPT_NO field in the DATA_JSON to add single quotes to the data I.e. how to add single quotes to as below

'EMPLOYEE_DEPT_NO':'3'

Any help is much appreciated

I want the value of attribute inside JSON to be encoded with single quotes

Does this answer your question? [Parse JSON in TSQL](https://stackoverflow.com/questions/2867501/parse-json-in-tsql) — Adam Spriggs, Jun 10 '23 at 20:21
Please update your question to include your query -- even if it doesn't work. — Adam Spriggs, Jun 10 '23 at 20:22
For a numerical value (integer or floating point), unquoted is valid json — Hans Keﬆing, Jun 11 '23 at 13:54

score 0 · Answer 1 · answered Jun 11 '23 at 13:44

Select the field, convert it to (VAR)CHAR, and update it:

DROP TABLE IF EXISTS SAMPLE;
CREATE TABLE SAMPLE (
  ID TEXT,
  DATA_JSON NVARCHAR(MAX));

INSERT INTO SAMPLE VALUES
('123456', N'{"EMPLOYEE_ID":"12345678",
              "EMPLOYEE_DEPT":"SALES", 
              "EMPLOYEE_START_DATE":"2022-08-01",
              "EMPLOYEE_HIRE_DATE":"2022-07-15",
              "EMPLOYEE_NAME":"TEST",
              "EMPLOYEE_DEPT_NO":1,
              "EMPLOYEE_DEPT_CAT":"SL_LOB", 
              "EMPLOYEE_EMAIL_ADD":"ABCTEST@DOESNOTEXIST.ca",
              "EMPLOYEE_TYPE":"F"}');



SELECT * FROM SAMPLE;
-- Selecting the numeric field:
SELECT JSON_VALUE(DATA_JSON, '$.EMPLOYEE_DEPT_NO')  FROM SAMPLE;

-- Updating the numeric field using CAST:
UPDATE SAMPLE
SET DATA_JSON = JSON_MODIFY(DATA_JSON, '$.EMPLOYEE_DEPT_NO', CAST(JSON_VALUE(DATA_JSON, '$.EMPLOYEE_DEPT_NO') as VARCHAR(8))) FROM SAMPLE;

SELECT * FROM SAMPLE;

output:

(1 row affected)
ID                                                                                                                                                                                                                                                               DATA_JSON
---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------- ----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
123456                                                                                                                                                                                                                                                           {"EMPLOYEE_ID":"12345678",
              "EMPLOYEE_DEPT":"SALES", 
              "EMPLOYEE_START_DATE":"2022-08-01",
              "EMPLOYEE_HIRE_DATE":"2022-07-15",
              "EMPLOYEE_NAME":"TEST",
              "EMPLOYEE_DEPT_NO":1,
          

(1 row affected)


----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
1

(1 row affected)

(1 row affected)

ID                                                                                                                                                                                                                                                               DATA_JSON
---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------- ----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
123456                                                                                                                                                                                                                                                           {"EMPLOYEE_ID":"12345678",
              "EMPLOYEE_DEPT":"SALES", 
              "EMPLOYEE_START_DATE":"2022-08-01",
              "EMPLOYEE_HIRE_DATE":"2022-07-15",
              "EMPLOYEE_NAME":"TEST",
              "EMPLOYEE_DEPT_NO":"1",
        

(1 row affected)


Completion time: 2023-06-11T15:43:12.1263503+02:00

Ok, but (I do hope) there is a way of modifying an element in a JSON file, casting that value to an alphanumeric datatype ? — Luuk, Jun 11 '23 at 18:23

score -1 · Answer 2 · answered Jun 10 '23 at 20:32

To change the EMPLOYEE_DEPT_NO field in the DATA_JSON column to include single quotes, check the following query:-

UPDATE Incoming_data
SET Data_Json = REPLACE(Data_Json, '"EMPLOYEE_DEPT_NO":', "'EMPLOYEE_DEPT_NO':'")
WHERE Data_Json LIKE '%"EMPLOYEE_DEPT_NO":%';

SQL to add quotes to a attribute inside JSON

Incoming_data

2 Answers2