Update array within a dynamic JSON object. (Removing an element of array nested inside Json object)

SQL++
, ,
1 
  {
  "ADD": {
    "k_2": [
      {
        "allowed": false,
        "vs": "k_a"
      },
      {
        "allowed": false,
        "vs": "k_d"
      }
    ],
    "k_9": [
      {
        "allowed": false,
        "vs": "k_f"
      },
      {
        "allowed": true,
        "vs": "k_a"
      }
    ]
  },
  "REMOVE": {
    "k_4": [
      {
        "allowed": true,
        "vs": "k_a"
      }
    ],
    "k_7": [
      {
        "allowed": false,
        "vs": "k_g"
      },
      {
        "allowed": true,
        "vs": "k_a"
      }
    ]
  }
}

This is my sample document. I want to delete those objects from array whose “vs” value is something, let’s say “k_a”. So the final output will be -

{
  "ADD": {
    "k_2": [
      {
        "allowed": false,
        "vs": "k_d"
      }
    ],
    "k_9": [
      {
        "allowed": false,
        "vs": "k_f"
      }
    ]
  },
  "REMOVE": {
    "k_4": [],
    "k_7": [
      {
        "allowed": false,
        "vs": "k_g"
      }
    ]
  }
}

This is the query I tried, but it wasn’t working. Can anyone please tell what am I doing wrong?

UPDATE sourceprioritization sp
    SET obj = ARRAY v FOR v IN obj WHEN v.vs != "k_a" END
    FOR obj IN OBJECT_VALUES(sp.ADD) END WHERE META(sp).id = "SPT|O|789"

Note - Here the keywords “ADD” and “REMOVE” are static but anything of the form “k_number” or “k_letter” is dynamic, and can be anything.

Any help is highly appreciated.

2

Where clause controls if the document must mutate or not. SET clause controls what to mutate. You need the where clause to avoid unnecessary mutation (Example: If repeat same update second time there should not be any update, i.e mutation count should be 0 unless some one changed the document.).

UPDATE sourceprioritization sp USE KEYS "SPT|O|789"
    SET sp.[obj1.name].[obj.name] = ARRAY v FOR v IN obj.val WHEN v.vs != "k_a" END
    FOR obj IN OBJECT_PAIRS(obj1.val)
           FOR obj1 IN OBJECT_PAIRS(sp) WHEN obj1.name IN ["ADD", "REMOVE"] AND "k_a" IN obj.val[*].vs END
WHERE ANY obj1 IN OBJECT_PAIRS(sp) SATISFIES obj1.name IN ["ADD", "REMOVE"]
      AND (ANY obj IN OBJECT_PAIRS(obj1.val) SATISFIES "k_a" IN obj.val[*].vs END) END;

OR

change SET sp.[obj1.name].[obj.name] to SET obj1.val.[obj.name]

UPDATE sourceprioritization sp USE KEYS "SPT|O|789"
    SET obj1.val.[obj.name] = ARRAY v FOR v IN obj.val WHEN v.vs != "k_a" END
    FOR obj IN OBJECT_PAIRS(obj1.val)
           FOR obj1 IN OBJECT_PAIRS(sp) WHEN obj1.name IN ["ADD", "REMOVE"] AND "k_a" IN obj.val[*].vs END
WHERE ANY obj1 IN OBJECT_PAIRS(sp) SATISFIES obj1.name IN ["ADD", "REMOVE"]
      AND (ANY obj IN OBJECT_PAIRS(obj1.val) SATISFIES "k_a" IN obj.val[*].vs END) END;
3

Thanks a lot. I’ve understood the case for Array inside objects. Can you give me an insight on how to handle the same thing when there are nested objects? Let’s say I had this document -

{
  "ADD": {
    "k_2": {
      "k_a":  {
        "allowed": false
      },
      "k_d": {
        "allowed": false
      }
    },
    "k_9": {
      "k_f":  {
        "allowed": false
      },
    }
  },
  "REMOVE": {
    "k_4": {
      "k_a" : {
        "allowed": true
       }
    },
    "k_7": {
      "k_g": {
        "allowed": false
      },
      "k_a":  {
        "allowed": true
      }
    }
  }
}

Expected output - Objects with key “k_a” and key "k_9"removed (either in a single query or two different queries, whichever is a better way.)

4 
INSERT INTO default VALUES("k01", { "ADD": { "k_2": { "k_a":  { "allowed": false }, "k_d": { "allowed": false } }, "k_9": { "k_f":  { "allowed": false } } }, "REMOVE": { "k_4": { "k_a" : { "allowed": true } }, "k_7": { "k_g": { "allowed": false }, "k_a":  { "allowed": true } } } });

UPDATE default sp USE KEYS "k01"
    SET sp.[obj.name] = OBJECT obj1.name: (OBJECT obj2.name:obj2.val
                                           FOR obj2 IN OBJECT_PAIRS(obj1.val)
                                           WHEN obj2.name NOT IN ["k_9", "k_a"] END)
                              FOR obj1 IN OBJECT_PAIRS(obj.val)
                              WHEN obj1.name NOT IN ["k_9", "k_a"] END
           FOR obj IN OBJECT_PAIRS(sp) WHEN obj.name IN ["ADD", "REMOVE"] END
WHERE ANY obj IN OBJECT_PAIRS(sp) SATISFIES obj.name IN ["ADD", "REMOVE"]
      AND (ANY obj1 WITHIN OBJECT_PAIRS(obj.val) SATISFIES obj1.name IN ["k_a","k_9"] END) END;