N1QL Clone array and change some fields

SQL++
1

Hi,

I have a requirement where I need to clone an array and change some fields on the new array. Here’s an example of what I need:

    "MtrlUomIdList": [
      {
        "Bdc": "121389136",
        "CasePckQty": 1,
        "CasePckTyp": "REG",
       "EffDtRnge": {
          "EndDt": "2022-03-10",
          "StrtDt": "2019-06-17"
        }
      },
      {
        "Bdc": "121389136",
        "CasePckQty": 6,
        "EffDtRnge": {
          "EndDt": "2022-04-10",
          "StrtDt": "2019-06-17"
        }
      }
    ]

I need to clone the 2 items in the MtrlUomIdList and change the start and end dates so that the 2 existing items get end dated and the 2 new ones we add are made effective. The final output should look like this:

    "MtrlUomIdList": [
      {
        "Bdc": "121389136",
        "CasePckQty": 1,
        "CasePckTyp": "REG",
       "EffDtRnge": {
          "EndDt": "2022-05-10",
          "StrtDt": "2019-06-17"
        }
      },
      {
        "Bdc": "121389136",
        "CasePckQty": 6,
        "EffDtRnge": {
          "EndDt": "2022-05-10",
          "StrtDt": "2019-06-17"
        }
      },
   {
        "Bdc": "121389136",
        "CasePckQty": 1,
        "CasePckTyp": "REG",
       "EffDtRnge": {
          "EndDt": "2022-05-11",
          "StrtDt": "9999-01-01"
        }
      },
      {
        "Bdc": "121389136",
        "CasePckQty": 6,
        "EffDtRnge": {
          "EndDt": "2022-05-11",
          "StrtDt": "9999-01-01"
        }
      }
    ]

Is there a way to make this happen via a N1QL update?

2 
UPDATE default AS d
d.MtrlUomIdList = ARRAY_CONCAT(ARRAY OBJECT_CONCAT(v, {"EffDtRnge":{v.StrtDt, "EndDt": "2022-05-11"}}) FOR v IN d.MtrlUomIdList END,
                               ARRAY OBJECT_CONCAT(v, {"EffDtRnge":{"StrtDt":"2022-05-11", "EndDt": "9999-01-01"}}) FOR v IN d.MtrlUomIdList END
                                )
WHERE ......

You can run following SELECT see meets your expectation

WITH d AS ({"MtrlUomIdList": [ { "Bdc": "121389136", "CasePckQty": 1, "CasePckTyp": "REG", "EffDtRnge": { "EndDt": "2022-03-10", "StrtDt": "2019-06-17" } }, { "Bdc": "121389136", "CasePckQty": 6, "EffDtRnge": { "EndDt": "2022-04-10", "StrtDt": "2019-06-17" } } ]})
SELECT ARRAY_CONCAT(ARRAY OBJECT_CONCAT(v, {"EffDtRnge":{v.StrtDt, "EndDt": "2022-05-11"}}) FOR v IN d.MtrlUomIdList END,
                               ARRAY OBJECT_CONCAT(v, {"EffDtRnge":{"StrtDt":"2022-05-11", "EndDt": "9999-01-01"}}) FOR v IN d.MtrlUomIdList END
                                ) AS x
FROM d;
1 Like
3

You sir, are awesome. I got the Array_concat part right but was struggling on how to change the effective date section within each MtrlUomIdList. Did not think of object concat and that it would simply replace the EffDtRnge section in place. Thanks a lot for your help.

1 Like
4

Object must have unique fields it duplicate fields are replaced by second value . Other option cascade OBJECT_PUT()

I don’t know your use case

In addition to above
It copies only EndDt “9999-01-01” only. repeated update with same startDate “2022-05-11” will not make copies.

CASE WHEN FIRST vv FOR vv IN d.MtrlUomIdList WHEN vv.EffDtRnge.EndDt == "9999-01-01" END IS MISSING
     THEN ARRAY_CONCAT(ARRAY OBJECT_CONCAT(v, {"EffDtRnge":{v.StrtDt, "EndDt": "2022-05-11"}}) FOR v IN d.MtrlUomIdList END,
                       ARRAY OBJECT_CONCAT(v, {"EffDtRnge":{"StrtDt":"2022-05-11", "EndDt": "9999-01-01"}}) FOR v IN d.MtrlUomIdList END
                      )
     ELSE ARRAY_CONCAT( ARRAY v FOR v IN d.MtrlUomIdList WHEN v.EffDtRnge.EndDt != "9999-01-01" END,
                        ARRAY OBJECT_CONCAT(v, {"EffDtRnge":{v.StrtDt, "EndDt": "2022-05-11"}})
                        FOR v IN d.MtrlUomIdList WHEN v.EffDtRnge.EndDt == "9999-01-01" AND v.StrtDt != "2022-05-11"
                        END,
                        ARRAY OBJECT_CONCAT(v, {"EffDtRnge":{"StrtDt":"2022-05-11", v.EndDt}})
                        FOR v IN d.MtrlUomIdList WHEN v.EffDtRnge.EndDt == "9999-01-01
                        END)
END