Using variable to access Array

zachMtech · 3 April 2024 13:34

I'm working with Node-RED and using JSONata for expression handling. In my setup, I have an array named SMT where each element contains a node named item . Additionally, I have a variable named count that I intend to use as an index to access specific elements within the SMT array. However, when I try to use the syntax SMT[Count].item to access the item node of the array element at the index specified by count , it is not recognized. How can I correctly access the item node of an element within the SMT array using the count variable as an index in a JSONata expression?

I was able to achieve the same results with writing to a msg.Context, breaking up the Array into objects, but I am curious if there is a standard syntax that I am missing or is this just a bad practice.

knolleary · 3 April 2024 14:01

Hi @zachMtech

How are these properties structured on the msg object itself? Are they under msg.payload or toplevel properties like msg.SMT and msg.Count ?

Depending on the specifics, you could try SMT[$$.Count].item

jbudd · 3 April 2024 14:02

Hello @zachMtech and welcome to the forum.

I wish you had included an example of your array but nevertheless...

Probably this is just a typo but your post has "Count" in some places and "count" in others.

zachMtech · 3 April 2024 15:39

Yes this is what worked. I am not very familiar with the JSONata Syntax, just started using node red this week.

Thank you for the fast response.

zachMtech · 3 April 2024 15:52

For those interested the msg Structure is as follows. I originally was going to use the msg.Count to loop through SMT, but since I did not understand the correct Syntax I ended up using a Split Node and writing to msg.Context

{
    "payload": {
        "trans_num": 78511016,
        "trans_type": "S",
        "trans_date": "2024-01-02T00:00:00.000Z",
        "item": "FSB36-SD24-MB",
        "qty": -1,
        "whse": "MAIN",
        "loc": "HC00298883",
        "ref_type": "O",
        "ref_num": "HC00298883",
        "ref_line_suf": 10,
        "cost": 26.77318,
        "user_code": null,
        "lot": null,
        "ref_release": 0,
        "reason_code": null,
        "backflush": 0,
        "wc": null,
        "awaiting_eop": 0,
        "matl_cost": 26.77318,
        "lbr_cost": 0,
        "fovhd_cost": 0,
        "vovhd_cost": 0,
        "out_cost": 0,
        "cost_code": null,
        "NoteExistsFlag": 0,
        "RecordDate": "2024-01-02T09:31:15.567Z",
        "RowPointer": "5FAB8DC4-370F-4AF7-B5EF-CA9BD61A2C7F",
        "CreatedBy": "$service",
        "UpdatedBy": "$service",
        "CreateDate": "2024-01-02T09:31:15.567Z",
        "InWorkflow": 0,
        "document_num": null,
        "import_doc_id": null,
        "uf_jit_reference": null,
        "count_sequence": null,
        "Uf_Trans_Num": null,
        "Uf_JE_Customer": null,
        "uf_jit_DocID": "",
        "uf_jit_DocLine": "",
        "uf_jit_parent": "",
        "uf_jit_QAID": "",
        "uf_co_number": "",
        "uf_user": "",
        "emp_num": null,
        "job_lot": null,
        "job_ser_num": null,
        "uf_IdealReference": null,
        "uf_WMSTransNum": null
    },
    "topic": "",
    "query": "SELECT top 2 mt.* \r\nFROM dbo.matltran mt\r\nJOIN dbo.item i ON i.item = mt.Item\r\nWHERE mt.uf_WMSTransNum IS NULL AND mt.trans_date > '01/01/24' and mt.whse = 'MAIN'\r\nORDER BY mt.trans_date ",
    "queryMode": "query",
    "queryParams": [],
    "sqlInfo": [],
    "SMT": [
        {
            "trans_num": 78511015,
            "trans_type": "S",
            "trans_date": "2024-01-02T00:00:00.000Z",
            "item": "FSB36-GMB",
            "qty": -1,
            "whse": "MAIN",
            "loc": "HC00298883",
            "ref_type": "O",
            "ref_num": "HC00298883",
            "ref_line_suf": 9,
            "cost": 82.15359,
            "user_code": null,
            "lot": null,
            "ref_release": 0,
            "reason_code": null,
            "backflush": 0,
            "wc": null,
            "awaiting_eop": 0,
            "matl_cost": 82.15359,
            "lbr_cost": 0,
            "fovhd_cost": 0,
            "vovhd_cost": 0,
            "out_cost": 0,
            "cost_code": null,
            "NoteExistsFlag": 0,
            "RecordDate": "2024-01-02T09:31:13.753Z",
            "RowPointer": "033B56BC-09FB-4812-A399-F3F341B8A6C2",
            "CreatedBy": "$service",
            "UpdatedBy": "$service",
            "CreateDate": "2024-01-02T09:31:13.753Z",
            "InWorkflow": 0,
            "document_num": null,
            "import_doc_id": null,
            "uf_jit_reference": null,
            "count_sequence": null,
            "Uf_Trans_Num": null,
            "Uf_JE_Customer": null,
            "uf_jit_DocID": "",
            "uf_jit_DocLine": "",
            "uf_jit_parent": "",
            "uf_jit_QAID": "",
            "uf_co_number": "",
            "uf_user": "",
            "emp_num": null,
            "job_lot": null,
            "job_ser_num": null,
            "uf_IdealReference": null,
            "uf_WMSTransNum": null
        },
        {
            "trans_num": 78511016,
            "trans_type": "S",
            "trans_date": "2024-01-02T00:00:00.000Z",
            "item": "FSB36-SD24-MB",
            "qty": -1,
            "whse": "MAIN",
            "loc": "HC00298883",
            "ref_type": "O",
            "ref_num": "HC00298883",
            "ref_line_suf": 10,
            "cost": 26.77318,
            "user_code": null,
            "lot": null,
            "ref_release": 0,
            "reason_code": null,
            "backflush": 0,
            "wc": null,
            "awaiting_eop": 0,
            "matl_cost": 26.77318,
            "lbr_cost": 0,
            "fovhd_cost": 0,
            "vovhd_cost": 0,
            "out_cost": 0,
            "cost_code": null,
            "NoteExistsFlag": 0,
            "RecordDate": "2024-01-02T09:31:15.567Z",
            "RowPointer": "5FAB8DC4-370F-4AF7-B5EF-CA9BD61A2C7F",
            "CreatedBy": "$service",
            "UpdatedBy": "$service",
            "CreateDate": "2024-01-02T09:31:15.567Z",
            "InWorkflow": 0,
            "document_num": null,
            "import_doc_id": null,
            "uf_jit_reference": null,
            "count_sequence": null,
            "Uf_Trans_Num": null,
            "Uf_JE_Customer": null,
            "uf_jit_DocID": "",
            "uf_jit_DocLine": "",
            "uf_jit_parent": "",
            "uf_jit_QAID": "",
            "uf_co_number": "",
            "uf_user": "",
            "emp_num": null,
            "job_lot": null,
            "job_ser_num": null,
            "uf_IdealReference": null,
            "uf_WMSTransNum": null
        }
    ],
    "parts": {
        "id": "5ee43869b269c348",
        "type": "array",
        "count": 2,
        "len": 1,
        "index": 1
    },
    "_msgid": "a5eb9f7d20828072",
    "Count": 1,
    "Context": {
        "trans_num": 78511016,
        "trans_type": "S",
        "trans_date": "2024-01-02T00:00:00.000Z",
        "item": "FSB36-SD24-MB",
        "qty": -1,
        "whse": "MAIN",
        "loc": "HC00298883",
        "ref_type": "O",
        "ref_num": "HC00298883",
        "ref_line_suf": 10,
        "cost": 26.77318,
        "user_code": null,
        "lot": null,
        "ref_release": 0,
        "reason_code": null,
        "backflush": 0,
        "wc": null,
        "awaiting_eop": 0,
        "matl_cost": 26.77318,
        "lbr_cost": 0,
        "fovhd_cost": 0,
        "vovhd_cost": 0,
        "out_cost": 0,
        "cost_code": null,
        "NoteExistsFlag": 0,
        "RecordDate": "2024-01-02T09:31:15.567Z",
        "RowPointer": "5FAB8DC4-370F-4AF7-B5EF-CA9BD61A2C7F",
        "CreatedBy": "$service",
        "UpdatedBy": "$service",
        "CreateDate": "2024-01-02T09:31:15.567Z",
        "InWorkflow": 0,
        "document_num": null,
        "import_doc_id": null,
        "uf_jit_reference": null,
        "count_sequence": null,
        "Uf_Trans_Num": null,
        "Uf_JE_Customer": null,
        "uf_jit_DocID": "",
        "uf_jit_DocLine": "",
        "uf_jit_parent": "",
        "uf_jit_QAID": "",
        "uf_co_number": "",
        "uf_user": "",
        "emp_num": null,
        "job_lot": null,
        "job_ser_num": null,
        "uf_IdealReference": null,
        "uf_WMSTransNum": null
    }
}

Colin · 3 April 2024 15:54

In case you did not realise, you don't have to use JSONata. There are always other ways. Personally I only use it for the simplest cases.

system · 17 April 2024 15:55

This topic was automatically closed 14 days after the last reply. New replies are no longer allowed.

Topic		Replies	Views
Jsonata: return value from context array General	2	4553	5 December 2018
(Solved) JSONata - variable to determine correct entry of an Array General	2	1832	27 December 2018
JSONata absurdities General	6	513	28 June 2020
Jsonata globalContext() inconsistent handling of string vs variable General	7	107	7 May 2023
Jsonata help once again please guys General	11	322	27 October 2022

Using variable to access Array

Related Topics