SQL Query Selecting Different Row Result in JSON_Modify Because of in Operator Provided Value

SQL query selecting different row result in JSON_MODIFY because of In operator provided value

You cannot expect JSON_MODIFY to run multiple times recursively using different rows. Each execution will run once independently per row, so each object is being applied to the original JSON separately.

If you remove where seqnum = 1 you will see what I mean.

---

Instead, I think you should just rebuild the whole JSON object from scratch.

This is made more complicated by the fact that you don't want to delete anything from the JSON that isn't in your existing rows

• We begin by filtering commonformsschema to only the rows you want, taking All into account.
• Then, within an APPLY, we take the rows we need from ApplicationRoles....
• ... and crack open the existing JSON into separate rows
• ... and FULL JOIN them together (we need FULL because Default doesn't exist in the table)
• We construct JSON out of that using explicit path syntax, and a root roles object.
• I've put in an ORDER BY to keep the ordering of the original JSON, but that is not necessary if you don't care.
• The APPLY subquery can also be done as a correlated subquery directly in the SELECT, as you have done.

SELECT
    fs1.SchemaId,
    j.newJson
FROM (
    SELECT *,
        row_number() over (partition by fs1.schemaName
                   order by case when fs1.tenant = 'ALL'
                              then 2 else 1 end,
                            fs1.tenant
                             ) as seqnum
    FROM commonformsschema fs1
    WHERE fs1.Tenant IN ( 'constructiontest', 'All' )
) fs1
CROSS APPLY (
    SELECT
        ISNULL(ar1.rolename, j2.role) role,
        ISNULL(ar1.[create], j2.[create]) [permissions.create],
        ISNULL(ar1.[read], j2.[read]) [permissions.read],
        ISNULL(ar1.[update], j2.[update]) [permissions.update],
        ISNULL(ar1.[delete], j2.[delete]) [permissions.delete]
    FROM (
        SELECT *
        FROM ApplicationRoles ar1
        WHERE ar1.SchemaId = fs1.SchemaId
          AND ar1.RoleName IN (
            'Construction Manager Admin',
            'Project Manager Admin',
            'Read' )
    ) ar1
    FULL JOIN OPENJSON (fs1.[Schema], '$.roles') j1
        CROSS APPLY OPENJSON (j1.value)
            WITH (
              [role] nvarchar(100),
              [create] bit '$.permissions.create',
              [read] bit '$.permissions.read',
              [update] bit '$.permissions.update',
              [delete] bit '$.permissions.delete'
            ) j2
      ON j2.[role] = ar1.rolename
    WHERE (fs1.ModifiedDateTime > '2021-07-12 04:25:57.0000000' OR
           ar1.ModifiedDateTime > '2021-07-12 04:25:57.0000000')
    ORDER BY ISNULL(j1.[key], '9999')
    FOR JSON PATH, INCLUDE_NULL_VALUES, ROOT ('roles')
) j(newJson)
WHERE fs1.seqnum = 1
  AND j.newJson IS NOT NULL;

db<>fiddle.uk

JSON_MODIFY append $..... already exist instead update,duplicating value

append is only used if you want to add another object to the array.

If you want to modify the existing object, use the array index:

SELECT json_modify(fs1.[Schema], '$.roles[1]', json_query(
       (
              SELECT ar1.rolename AS [role],
                     ar1.[create] AS [permissions.create],
                     ar1.[read]   AS [permissions.read],
                     ar1.[update] AS [permissions.update],
                     ar1.[delete] AS [permissions.delete]
              FOR json path, without_array_wrapper
       ))
     ) AS [Schema]
FROM   applicationroles ar1
JOIN   commonformsschema fs1
  ON   ar1.schemaid = fs1.schemaid;

If you don't know whether to add or update, and you are checking based on rolename, instead you can query the current value's index, and update that.

This only works on SQL Server 2017

SELECT json_modify(fs1.[Schema],
      ISNULL(N'$.roles[' + j.[key] COLLATE Latin1_General_BIN2 + N']', N'append $.roles'), json_query(
       (
              SELECT ar1.rolename AS [role],
                     ar1.[create] AS [permissions.create],
                     ar1.[read]   AS [permissions.read],
                     ar1.[update] AS [permissions.update],
                     ar1.[delete] AS [permissions.delete]
              FOR json path, without_array_wrapper
       ))
     ) AS [Schema]
FROM   applicationroles ar1
JOIN   commonformsschema fs1
  ON   ar1.schemaid = fs1.schemaid
OUTER APPLY (
    SELECT TOP (1) j.[key]
    FROM OPENJSON(fs1.[Schema], '$.roles') j
    WHERE JSON_VALUE(j.value, '$.rolename') = ar1.rolename
) j;

SQL query to determine if a JSON value contains a specified attribute

I assume you're using MySQL 5.7, which adds the JSON data type. Use JSON_EXTRACT(colname, '$.cost') to access the cost property. It will be NULL is there's no such property.

1. WHERE JSON_EXTRACT(colname, '$.cost') IS NOT NULL
2. WHERE JSON_EXTRACT(colname, '$.cost') IS NULL
3. WHERE JSON_EXTRACT(colname, '$.cost') != ''

It will also be NULL if the value in the JSON is null; if you need to distinguish this case, see Can't detect null value from JSON_EXTRACT

How do I modify fields inside the new PostgreSQL JSON datatype?

Update: With PostgreSQL 9.5, there are some jsonb manipulation functionality within PostgreSQL itself (but none for json; casts are required to manipulate json values).

Merging 2 (or more) JSON objects (or concatenating arrays):

SELECT jsonb '{"a":1}' || jsonb '{"b":2}', -- will yield jsonb '{"a":1,"b":2}'
       jsonb '["a",1]' || jsonb '["b",2]'  -- will yield jsonb '["a",1,"b",2]'

So, setting a simple key can be done using:

SELECT jsonb '{"a":1}' || jsonb_build_object('<key>', '<value>')

Where <key> should be string, and <value> can be whatever type to_jsonb() accepts.

For setting a value deep in a JSON hierarchy, the jsonb_set() function can be used:

SELECT jsonb_set('{"a":[null,{"b":[]}]}', '{a,1,b,0}', jsonb '{"c":3}')
-- will yield jsonb '{"a":[null,{"b":[{"c":3}]}]}'

Full parameter list of jsonb_set():

jsonb_set(target         jsonb,
          path           text[],
          new_value      jsonb,
          create_missing boolean default true)

path can contain JSON array indexes too & negative integers that appear there count from the end of JSON arrays. However, a non-existing, but positive JSON array index will append the element to the end of the array:

SELECT jsonb_set('{"a":[null,{"b":[1,2]}]}', '{a,1,b,1000}', jsonb '3', true)
-- will yield jsonb '{"a":[null,{"b":[1,2,3]}]}'

For inserting into JSON array (while preserving all of the original values), the jsonb_insert() function can be used (in 9.6+; this function only, in this section):

SELECT jsonb_insert('{"a":[null,{"b":[1]}]}', '{a,1,b,0}', jsonb '2')
-- will yield jsonb '{"a":[null,{"b":[2,1]}]}', and
SELECT jsonb_insert('{"a":[null,{"b":[1]}]}', '{a,1,b,0}', jsonb '2', true)
-- will yield jsonb '{"a":[null,{"b":[1,2]}]}'

Full parameter list of jsonb_insert():

jsonb_insert(target       jsonb,
             path         text[],
             new_value    jsonb,
             insert_after boolean default false)

Again, negative integers that appear in path count from the end of JSON arrays.

So, f.ex. appending to an end of a JSON array can be done with:

SELECT jsonb_insert('{"a":[null,{"b":[1,2]}]}', '{a,1,b,-1}', jsonb '3', true)
-- will yield jsonb '{"a":[null,{"b":[1,2,3]}]}', and

However, this function is working slightly differently (than jsonb_set()) when the path in target is a JSON object's key. In that case, it will only add a new key-value pair for the JSON object when the key is not used. If it's used, it will raise an error:

SELECT jsonb_insert('{"a":[null,{"b":[1]}]}', '{a,1,c}', jsonb '[2]')
-- will yield jsonb '{"a":[null,{"b":[1],"c":[2]}]}', but
SELECT jsonb_insert('{"a":[null,{"b":[1]}]}', '{a,1,b}', jsonb '[2]')
-- will raise SQLSTATE 22023 (invalid_parameter_value): cannot replace existing key

Deleting a key (or an index) from a JSON object (or, from an array) can be done with the - operator:

SELECT jsonb '{"a":1,"b":2}' - 'a', -- will yield jsonb '{"b":2}'
       jsonb '["a",1,"b",2]' - 1    -- will yield jsonb '["a","b",2]'

Deleting, from deep in a JSON hierarchy can be done with the #- operator:

SELECT '{"a":[null,{"b":[3.14]}]}' #- '{a,1,b,0}'
-- will yield jsonb '{"a":[null,{"b":[]}]}'

For 9.4, you can use a modified version of the original answer (below), but instead of aggregating a JSON string, you can aggregate into a json object directly with json_object_agg().

Original answer: It is possible (without plpython or plv8) in pure SQL too (but needs 9.3+, will not work with 9.2)

CREATE OR REPLACE FUNCTION "json_object_set_key"(
  "json"          json,
  "key_to_set"    TEXT,
  "value_to_set"  anyelement
)
  RETURNS json
  LANGUAGE sql
  IMMUTABLE
  STRICT
AS $function$
SELECT concat('{', string_agg(to_json("key") || ':' || "value", ','), '}')::json
  FROM (SELECT *
          FROM json_each("json")
         WHERE "key" <> "key_to_set"
         UNION ALL
        SELECT "key_to_set", to_json("value_to_set")) AS "fields"
$function$;

SQLFiddle

Edit:

A version, which sets multiple keys & values:

CREATE OR REPLACE FUNCTION "json_object_set_keys"(
  "json"          json,
  "keys_to_set"   TEXT[],
  "values_to_set" anyarray
)
  RETURNS json
  LANGUAGE sql
  IMMUTABLE
  STRICT
AS $function$
SELECT concat('{', string_agg(to_json("key") || ':' || "value", ','), '}')::json
  FROM (SELECT *
          FROM json_each("json")
         WHERE "key" <> ALL ("keys_to_set")
         UNION ALL
        SELECT DISTINCT ON ("keys_to_set"["index"])
               "keys_to_set"["index"],
               CASE
                 WHEN "values_to_set"["index"] IS NULL THEN 'null'::json
                 ELSE to_json("values_to_set"["index"])
               END
          FROM generate_subscripts("keys_to_set", 1) AS "keys"("index")
          JOIN generate_subscripts("values_to_set", 1) AS "values"("index")
         USING ("index")) AS "fields"
$function$;

Edit 2: as @ErwinBrandstetter noted these functions above works like a so-called UPSERT (updates a field if it exists, inserts if it does not exist). Here is a variant, which only UPDATE:

CREATE OR REPLACE FUNCTION "json_object_update_key"(
  "json"          json,
  "key_to_set"    TEXT,
  "value_to_set"  anyelement
)
  RETURNS json
  LANGUAGE sql
  IMMUTABLE
  STRICT
AS $function$
SELECT CASE
  WHEN ("json" -> "key_to_set") IS NULL THEN "json"
  ELSE (SELECT concat('{', string_agg(to_json("key") || ':' || "value", ','), '}')
          FROM (SELECT *
                  FROM json_each("json")
                 WHERE "key" <> "key_to_set"
                 UNION ALL
                SELECT "key_to_set", to_json("value_to_set")) AS "fields")::json
END
$function$;

Edit 3: Here is recursive variant, which can set (UPSERT) a leaf value (and uses the first function from this answer), located at a key-path (where keys can only refer to inner objects, inner arrays not supported):

CREATE OR REPLACE FUNCTION "json_object_set_path"(
  "json"          json,
  "key_path"      TEXT[],
  "value_to_set"  anyelement
)
  RETURNS json
  LANGUAGE sql
  IMMUTABLE
  STRICT
AS $function$
SELECT CASE COALESCE(array_length("key_path", 1), 0)
         WHEN 0 THEN to_json("value_to_set")
         WHEN 1 THEN "json_object_set_key"("json", "key_path"[l], "value_to_set")
         ELSE "json_object_set_key"(
           "json",
           "key_path"[l],
           "json_object_set_path"(
             COALESCE(NULLIF(("json" -> "key_path"[l])::text, 'null'), '{}')::json,
             "key_path"[l+1:u],
             "value_to_set"
           )
         )
       END
  FROM array_lower("key_path", 1) l,
       array_upper("key_path", 1) u
$function$;

Updated: Added function for replacing an existing json field's key by another given key. Can be in handy for updating data types in migrations or other scenarios like data structure amending.

CREATE OR REPLACE FUNCTION json_object_replace_key(
    json_value json,
    existing_key text,
    desired_key text)
  RETURNS json AS
$BODY$
SELECT COALESCE(
(
    SELECT ('{' || string_agg(to_json(key) || ':' || value, ',') || '}')
    FROM (
        SELECT *
        FROM json_each(json_value)
        WHERE key <> existing_key
        UNION ALL
        SELECT desired_key, json_value -> existing_key
    ) AS "fields"
    -- WHERE value IS NOT NULL (Actually not required as the string_agg with value's being null will "discard" that entry)

),
    '{}'
)::json
$BODY$
  LANGUAGE sql IMMUTABLE STRICT
  COST 100;

Update: functions are compacted now.

How to use json column in the WHERE clause as a condition

Use JSON_VALUE:

SELECT t.*
FROM tableA t
WHERE JSON_VALUE(col3, '$.key') LIKE 'some_value'

This assumes that the column which contains the JSON value {'key':'value'} is called col3.

Insert an object into a JSON array in SQL Server

You should wrap the third parameter of your JSON_MODIFY statement with JSON_QUERY():

UPDATE TheTable 
SET TheJSON = JSON_MODIFY(TheJSON, 'append $', JSON_QUERY(N'{"id": 3, "name": "Three"}')) 
WHERE Condition = 1;

Here is a complete sample:

DECLARE @TheTable table(TheJSON nvarchar(max), Condition int )
DECLARE @mystring nvarchar(100)='{"id": 3, "name": "Three"}'

INSERT INTO @TheTable SELECT '[{"id": 1, "name": "One"}, {"id": 2, "name": "Two"}]', 1

UPDATE @TheTable 
SET TheJSON = JSON_MODIFY(TheJSON, 'append $', JSON_QUERY(N'{"id": 3, "name": "Three"}')) 
WHERE Condition = 1;

SELECT TheJSON FROM @TheTable

This is the final output:

[{"id": 1, "name": "One"}, {"id": 2, "name": "Two"},{"id": 3, "name": "Three"}]

More info on JSON_QUERY here, and the explanation of the issue is here.

---

Related Topics