SQL server Json with single array

Question

I am storing ids in comma separated string.

e.g

 1,2,3,4

How can I store this in JSON in the column and should be able to insert delete any particular value?

Thanks

You should at least post some information about the table structure, the desired JSON output and about what you've tried so far. — Onkel Toob, Apr 09 '18 at 12:14
I have done google but din't find any example showing single value in json array, its using key value pairs — Md. Parvez Alam, Apr 09 '18 at 12:15
`SELECT * FROM OPENJSON('[1,2,3,4]') WITH ([value] INT '$')`; `SELECT '[' + STRING_AGG([value],',') + ']' FROM (SELECT * FROM OPENJSON('[1,2,3,4]') WITH ([value] INT '$') EXCEPT SELECT 3) _`. In short: don't do this if you can help it. Use properly normalized tables if updating is something you'll be doing frequently. JSON support is not there as a way to support improperly normalized tables. Even if your input is primarily JSON, it's trivial to convert tables back to JSON, so that's not really an excuse. — Jeroen Mostert, Apr 09 '18 at 12:45

Onkel Toob · Answer 1 · 2018-04-11T10:42:49.963

0

Part of the following answer comes from here, so all credits go there: https://stackoverflow.com/a/37844117/2695832

Here's a solution that enables you to store your string values in a JSON array in a table column. However, the should be able to insert delete any particular value part of your question is not totally clear to me.

DECLARE @source  VARCHAR(20);
SET @source = '1,2,3,4';

DECLARE @values TABLE
(
    [Id]  VARCHAR(20)
);

INSERT INTO @values
(
    [Id]
)
       SELECT 
           value
       FROM [STRING_SPLIT](@source, ',')
           WHERE RTRIM(value) <> '';

INSERT INTO @values ([Id]) VALUES ('5')

DELETE FROM @values WHERE Id = 2

SELECT  
JSON_QUERY('[' + STUFF(( SELECT ',' + '"' + Id + '"' 
FROM @values FOR XML PATH('')),1,1,'') + ']' ) ids  
FOR JSON PATH , WITHOUT_ARRAY_WRAPPER

This produces the following JSON object:

{"ids":["1","3","4","5"]}

The code might need some tweaking to completely match your needs since you're probably not using a table variable and also maybe want to use a numeric data type for your id values.

edited Apr 11 '18 at 10:42

answered Apr 09 '18 at 12:43

Onkel Toob

2,152
1
17
25

should be able to insert, means I can append one more id – Md. Parvez Alam Apr 09 '18 at 13:03
With the proposed solution you could insert whatever additional values you want into the table variable. – Onkel Toob Apr 09 '18 at 13:05
Could you please guide me how to delete any value – Md. Parvez Alam Apr 09 '18 at 13:08
Depending on your delete criteria for example: `DELETE FROM @values WHERE Id = 2` before the XML-JSON-statement. – Onkel Toob Apr 09 '18 at 13:10
Sorry I didn't get – Md. Parvez Alam Apr 09 '18 at 13:17
I added INSERT and DELETE statements. If this is still not sufficient you should really update your question to clarify what you're after. – Onkel Toob Apr 09 '18 at 13:24
You're right, I'm sorry. My last edit obviously broke the SQL code, some characters were cut off. Fixed the code. – Onkel Toob Apr 11 '18 at 10:44
'JSON_QUERY' is not a recognized built-in function name. – Md. Parvez Alam Apr 11 '18 at 10:52
This is probably a matter of your SQL Server version and its compatibility level: https://stackoverflow.com/questions/44699201/how-to-check-are-there-json-functions-by-sql-query – Onkel Toob Apr 11 '18 at 10:55

SQL server Json with single array

1 Answers1