More jsonata array filtering

General
1

I want to filter arrays for some key value with jsonata, eg:

... `exchange-document`[`$`.kind = "A2"].`bibliographic-data`...

but I am interested in more than one value so I have tried

... `exchange-document`[`$`.kind = "A2" or `$`.kind = "A1"].`bibliographic-data`...

or

... `exchange-document`[`$`.kind = "A*"].`bibliographic-data`...

or even

... `exchange-document`[`$`.kind != "B2"].`bibliographic-data`...

as well as various tries of using $contains()

but it doesn't seem to work. What would be the correct way of doing this? Thanks

2

Hard to help with no data. But may be the ` around the $
p.s. Triple backticks must be on their own line and backticks not periods.

3

The ` around the $ is because $ is a key, it is not meant to be a variable.

    {
        "id": "5f676886e1f92e36",
        "type": "inject",
        "z": "d5a279b1156d9af4",
        "name": "",
        "props": [
            {
                "p": "payload"
            },
            {
                "p": "topic",
                "vt": "str"
            }
        ],
        "repeat": "",
        "crontab": "",
        "once": false,
        "onceDelay": 0.1,
        "topic": "",
        "payload": "{\"ops:world-patent-data\":{\"$\":{\"xmlns\":\"http://www.epo.org/exchange\",\"xmlns:ops\":\"http://ops.epo.org\",\"xmlns:xlink\":\"http://www.w3.org/1999/xlink\"},\"exchange-documents\":[{\"exchange-document\":[{\"$\":{\"system\":\"ops.epo.org\",\"family-id\":\"19913584\",\"country\":\"EP\",\"doc-number\":\"1499465\",\"kind\":\"A2\"},\"bibliographic-data\":[{\"publication-reference\":[{\"document-id\":[{\"$\":{\"document-id-type\":\"docdb\"},\"country\":[\"EP\"],\"doc-number\":[\"1499465\"],\"kind\":[\"A2\"],\"date\":[\"20050126\"]},{\"$\":{\"document-id-type\":\"epodoc\"},\"doc-number\":[\"EP1499465\"],\"date\":[\"20050126\"]}]}],\"classification-ipc\":[{\"text\":[\"B23B29/02\"]}],\"classifications-ipcr\":[{\"classification-ipcr\":[{\"$\":{\"sequence\":\"1\"},\"text\":[\"B23B  29/    02            A I                    \"]},{\"$\":{\"sequence\":\"2\"},\"text\":[\"F16F   7/    00            A I                    \"]},{\"$\":{\"sequence\":\"3\"},\"text\":[\"F16F   7/   108            A I                    \"]}]}],\"patent-classifications\":[{\"patent-classification\":[{\"$\":{\"sequence\":\"1\"},\"classification-scheme\":[{\"$\":{\"office\":\"EP\",\"scheme\":\"CPCI\"}}],\"section\":[\"B\"],\"class\":[\"23\"],\"subclass\":[\"B\"],\"main-group\":[\"29\"],\"subgroup\":[\"022\"],\"classification-value\":[\"I\"],\"generating-office\":[\"US\"]},{\"$\":{\"sequence\":\"2\"},\"classification-scheme\":[{\"$\":{\"office\":\"EP\",\"scheme\":\"CPCI\"}}],\"section\":[\"B\"],\"class\":[\"23\"],\"subclass\":[\"B\"],\"main-group\":[\"29\"],\"subgroup\":[\"022\"],\"classification-value\":[\"I\"],\"generating-office\":[\"EP\"]},{\"$\":{\"sequence\":\"3\"},\"classification-scheme\":[{\"$\":{\"office\":\"EP\",\"scheme\":\"CPCI\"}}],\"section\":[\"F\"],\"class\":[\"16\"],\"subclass\":[\"F\"],\"main-group\":[\"7\"],\"subgroup\":[\"108\"],\"classification-value\":[\"I\"],\"generating-office\":[\"US\"]},{\"$\":{\"sequence\":\"4\"},\"classification-scheme\":[{\"$\":{\"office\":\"EP\",\"scheme\":\"CPCI\"}}],\"section\":[\"F\"],\"class\":[\"16\"],\"subclass\":[\"F\"],\"main-group\":[\"7\"],\"subgroup\":[\"108\"],\"classification-value\":[\"I\"],\"generating-office\":[\"EP\"]}]}],\"application-reference\":[{\"$\":{\"doc-id\":\"16057428\"},\"document-id\":[{\"$\":{\"document-id-type\":\"docdb\"},\"country\":[\"EP\"],\"doc-number\":[\"03747542\"],\"kind\":[\"A\"]},{\"$\":{\"document-id-type\":\"epodoc\"},\"doc-number\":[\"EP20030747542\"],\"date\":[\"20030414\"]},{\"$\":{\"document-id-type\":\"original\"},\"doc-number\":[\"03747542\"]}]}],\"priority-claims\":[{\"priority-claim\":[{\"$\":{\"sequence\":\"1\",\"kind\":\"national\"},\"document-id\":[{\"$\":{\"document-id-type\":\"epodoc\"},\"doc-number\":[\"NO20020002077\"],\"date\":[\"20020430\"]},{\"$\":{\"document-id-type\":\"original\"},\"doc-number\":[\"20022077\"]}]},{\"$\":{\"sequence\":\"2\",\"kind\":\"national\"},\"document-id\":[{\"$\":{\"document-id-type\":\"epodoc\"},\"doc-number\":[\"WO2003NO00124\"],\"date\":[\"20030414\"]},{\"$\":{\"document-id-type\":\"original\"},\"doc-number\":[\"NO2003000124\"]}]}]}],\"parties\":[{\"applicants\":[{\"applicant\":[{\"$\":{\"sequence\":\"1\",\"data-format\":\"epodoc\"},\"applicant-name\":[{\"name\":[\"TEENESS ASA [NO]\"]}]},{\"$\":{\"sequence\":\"1\",\"data-format\":\"original\"},\"applicant-name\":[{\"name\":[\"TEENESS ASA\"]}]}]}],\"inventors\":[{\"inventor\":[{\"$\":{\"sequence\":\"1\",\"data-format\":\"epodoc\"},\"inventor-name\":[{\"name\":[\"DIGERNES ANDERS [NO]\"]}]},{\"$\":{\"sequence\":\"1\",\"data-format\":\"original\"},\"inventor-name\":[{\"name\":[\"DIGERNES, ANDERS\"]}]}]}]}],\"invention-title\":[{\"_\":\"DÄMPFUNGSVORRICHTUNG ZUR DÄMPFUNG VON SCHWINGUNGEN\",\"$\":{\"lang\":\"de\"}},{\"_\":\"APPAREIL AMORTISSEUR POUR AMORTIR DES VIBRATIONS\",\"$\":{\"lang\":\"fr\"}},{\"_\":\"DAMPING APPARATUS FOR THE DAMPING OF VIBRATIONS\",\"$\":{\"lang\":\"en\"}}]}]},{\"$\":{\"system\":\"ops.epo.org\",\"family-id\":\"19913584\",\"country\":\"EP\",\"doc-number\":\"1499465\",\"kind\":\"B1\"},\"bibliographic-data\":[{\"publication-reference\":[{\"document-id\":[{\"$\":{\"document-id-type\":\"docdb\"},\"country\":[\"EP\"],\"doc-number\":[\"1499465\"],\"kind\":[\"B1\"],\"date\":[\"20060830\"]},{\"$\":{\"document-id-type\":\"epodoc\"},\"doc-number\":[\"EP1499465\"],\"date\":[\"20060830\"]}]}],\"classifications-ipcr\":[{\"classification-ipcr\":[{\"$\":{\"sequence\":\"1\"},\"text\":[\"B23B  29/    02            A I                    \"]},{\"$\":{\"sequence\":\"2\"},\"text\":[\"F16F   7/    00            A I                    \"]},{\"$\":{\"sequence\":\"3\"},\"text\":[\"F16F   7/   108            A I                    \"]}]}],\"patent-classifications\":[{\"patent-classification\":[{\"$\":{\"sequence\":\"1\"},\"classification-scheme\":[{\"$\":{\"office\":\"EP\",\"scheme\":\"CPCI\"}}],\"section\":[\"B\"],\"class\":[\"23\"],\"subclass\":[\"B\"],\"main-group\":[\"29\"],\"subgroup\":[\"022\"],\"classification-value\":[\"I\"],\"generating-office\":[\"US\"]},{\"$\":{\"sequence\":\"2\"},\"classification-scheme\":[{\"$\":{\"office\":\"EP\",\"scheme\":\"CPCI\"}}],\"section\":[\"B\"],\"class\":[\"23\"],\"subclass\":[\"B\"],\"main-group\":[\"29\"],\"subgroup\":[\"022\"],\"classification-value\":[\"I\"],\"generating-office\":[\"EP\"]},{\"$\":{\"sequence\":\"3\"},\"classification-scheme\":[{\"$\":{\"office\":\"EP\",\"scheme\":\"CPCI\"}}],\"section\":[\"F\"],\"class\":[\"16\"],\"subclass\":[\"F\"],\"main-group\":[\"7\"],\"subgroup\":[\"108\"],\"classification-value\":[\"I\"],\"generating-office\":[\"US\"]},{\"$\":{\"sequence\":\"4\"},\"classification-scheme\":[{\"$\":{\"office\":\"EP\",\"scheme\":\"CPCI\"}}],\"section\":[\"F\"],\"class\":[\"16\"],\"subclass\":[\"F\"],\"main-group\":[\"7\"],\"subgroup\":[\"108\"],\"classification-value\":[\"I\"],\"generating-office\":[\"EP\"]}]}],\"application-reference\":[{\"$\":{\"doc-id\":\"16057428\"},\"document-id\":[{\"$\":{\"document-id-type\":\"docdb\"},\"country\":[\"EP\"],\"doc-number\":[\"03747542\"],\"kind\":[\"A\"]},{\"$\":{\"document-id-type\":\"epodoc\"},\"doc-number\":[\"EP20030747542\"],\"date\":[\"20030414\"]},{\"$\":{\"document-id-type\":\"original\"},\"doc-number\":[\"03747542\"]}]}],\"priority-claims\":[{\"priority-claim\":[{\"$\":{\"sequence\":\"1\",\"kind\":\"national\"},\"document-id\":[{\"$\":{\"document-id-type\":\"epodoc\"},\"doc-number\":[\"NO20020002077\"],\"date\":[\"20020430\"]},{\"$\":{\"document-id-type\":\"original\"},\"doc-number\":[\"20022077\"]}]},{\"$\":{\"sequence\":\"2\",\"kind\":\"national\"},\"document-id\":[{\"$\":{\"document-id-type\":\"epodoc\"},\"doc-number\":[\"WO2003NO00124\"],\"date\":[\"20030414\"]},{\"$\":{\"document-id-type\":\"original\"},\"doc-number\":[\"NO2003000124\"]}]}]}],\"parties\":[{\"applicants\":[{\"applicant\":[{\"$\":{\"sequence\":\"1\",\"data-format\":\"epodoc\"},\"applicant-name\":[{\"name\":[\"TEENESS ASA [NO]\"]}]},{\"$\":{\"sequence\":\"1\",\"data-format\":\"original\"},\"applicant-name\":[{\"name\":[\"TEENESS ASA\"]}]}]}],\"inventors\":[{\"inventor\":[{\"$\":{\"sequence\":\"1\",\"data-format\":\"epodoc\"},\"inventor-name\":[{\"name\":[\"DIGERNES ANDERS [NO]\"]}]},{\"$\":{\"sequence\":\"1\",\"data-format\":\"original\"},\"inventor-name\":[{\"name\":[\"DIGERNES, ANDERS\"]}]}]}]}],\"invention-title\":[{\"_\":\"DÄMPFUNGSVORRICHTUNG ZUR DÄMPFUNG VON SCHWINGUNGEN\",\"$\":{\"lang\":\"de\"}},{\"_\":\"APPAREIL AMORTISSEUR POUR AMORTIR DES VIBRATIONS\",\"$\":{\"lang\":\"fr\"}},{\"_\":\"DAMPING APPARATUS FOR THE DAMPING OF VIBRATIONS\",\"$\":{\"lang\":\"en\"}}]}]}]}]}}",
        "payloadType": "json",
        "x": 370,
        "y": 440,
        "wires": [
            [
                "4f80779aeb75f948"
            ]
        ]
    },
    {
        "id": "4f80779aeb75f948",
        "type": "change",
        "z": "d5a279b1156d9af4",
        "name": "",
        "rules": [
            {
                "t": "set",
                "p": "payload.test",
                "pt": "msg",
                "to": "$zip(**.`exchange-document`[`$`.kind = \"B\"].`bibliographic-data`.`publication-reference`.`document-id`[`$`.`document-id-type`=\"docdb\"].\t$join([**.country, **.`doc-number`]), **.`exchange-document`.`bibliographic-data`.`publication-reference`.\t`document-id`.$zip(country,`doc-number`,kind, date),**.`family-id`)",
                "tot": "jsonata"
            }
        ],
        "action": "",
        "property": "",
        "from": "",
        "to": "",
        "reg": false,
        "x": 640,
        "y": 460,
        "wires": [
            [
                "461be6079dc1ad01"
            ]
        ]
    },
    {
        "id": "461be6079dc1ad01",
        "type": "debug",
        "z": "d5a279b1156d9af4",
        "name": "debug 86",
        "active": true,
        "tosidebar": true,
        "console": false,
        "tostatus": false,
        "complete": "false",
        "statusVal": "",
        "statusType": "auto",
        "x": 880,
        "y": 440,
        "wires": []
    }
] ```
4

try

**.'exchange-document'['$'.kind = "B1"].'bibliographic-data'[0].
`publication-reference`[0].`document-id`[`$`.`document-id-type`= "docdb"].
$join([country, `doc-number`])[]

p.s. that is one messed up object

5

Thanks. I have managed to filter by one value, e.g. B1, but as they can be A1, A2, B1, B2, B8, etc I would like to filter by either a wildcard, e.g. B*, or an or statement, e.g. B1 or B2.

6 
[$substring(`$`.kind, 0,1) ="B"]
7

Perfect, thanks

8

As my new question would get the same heading, I will not create a new post.

Perhaps @E1cid can help me out again.

In an array of arrays, if the value in the [z][y][0] position is "EP", I would like the query to give me the value in the [z][y][1] position.

So in the below sample flow, which is the closest I've got to a solution, payload[0][0][0] is "EP" so I would like the query to give me the value of payload[0][0][1], i.e. 3812066.

[
    {
        "id": "f97084c3a7ab684e",
        "type": "inject",
        "z": "d5a279b1156d9af4",
        "name": "",
        "props": [
            {
                "p": "payload"
            },
            {
                "p": "topic",
                "vt": "str"
            }
        ],
        "repeat": "",
        "crontab": "",
        "once": false,
        "onceDelay": 0.1,
        "topic": "",
        "payload": "[[[\"EP\",\"3812066\",\"A1\",\"20210428\"],\"68342579\"],[[\"CN\",\"114555268\",\"A\",\"20220527\"],\"68342579\"],[[\"WO\",\"2021078416\",\"A1\",\"20210429\"],\"68342579\"]]",
        "payloadType": "json",
        "x": 950,
        "y": 860,
        "wires": [
            [
                "936f0cf620f5c86a"
            ]
        ]
    },
    {
        "id": "936f0cf620f5c86a",
        "type": "change",
        "z": "d5a279b1156d9af4",
        "name": "",
        "rules": [
            {
                "t": "set",
                "p": "payload",
                "pt": "msg",
                "to": "$[0][$[0] =\"EP\"].$[1]\t",
                "tot": "jsonata"
            }
        ],
        "action": "",
        "property": "",
        "from": "",
        "to": "",
        "reg": false,
        "x": 1100,
        "y": 860,
        "wires": [
            [
                "77821c9716abc85a"
            ]
        ]
    },
    {
        "id": "77821c9716abc85a",
        "type": "debug",
        "z": "d5a279b1156d9af4",
        "name": "debug 87",
        "active": true,
        "tosidebar": true,
        "console": false,
        "tostatus": false,
        "complete": "true",
        "targetType": "full",
        "statusVal": "",
        "statusType": "auto",
        "x": 1260,
        "y": 860,
        "wires": []
    }
]
9

Sorry not understanding
please show what output you would like from the input payload supplied

10

So, in the array of arrays I want to search for a value in position [0] in the deepest array and if I find it I want to output the value in position [1] in the same array. An example would be to search for "EP" in the first position in three arrays in payload[0]. As you can see, the array in payload[0][0] has "EP" in the [0] position, so my query should output the value in the [1] position in the same array, i.e. payload[0][0][1] = "3812006"

[
    [
        [
            "EP",
            "3812066",
            "A1",
            "20210428"
        ],
        "68342579"
    ],
    [
        [
            "CN",
            "114555268",
            "A",
            "20220527"
        ],
        "68342579"
    ],
    [
        [
            "WO",
            "2021078416",
            "A1",
            "20210429"
        ],
        "68342579"
    ]
]
11

I will ask again, please supply what the output should look like for the input array supplied.
It will make things totally clear to me.

12

"3812006"

13

so form the above array you want the output to be

if ep at position 0 in first element, what about other elements?
If incorrect please supply what the whole output should be from input array supplied.

14

Yes, that is correct. The output should be "3821066" if "EP" or "114555268" if "CN" is found, etc.

It would also be interesting to know how the output becomes "68342579" if "EP" is found (although this value is the same in all arrays, but as an example).

15

I give up, How hard is it to give an example of what the output should be. Same as you gave an example of input.

16

If “3821066“ is not an example of a wanted output then I don’t know what you mean by output.

17

In that case do it with javascript. It will be a bit more efficient anyway.
As you can see, we have failed to fully grasp your description of the problem.

18

Perhaps you just mean that you want msg.payload[0][0][1]

19

It was another example to make it more clear, obviously failed.

20

That would indeed give me that output, but then I need to know where the value is.

I’m interested in arrays containing “EP” in a given position, and if I find it I want to output a value from another position in the same array.

I search for a value out of a small set of two-character strings (i.e. easy to find) and thereby get the value I’m after (impossible to find directly as it is basically random).

The issue is that they are on the same level in the array and I don’t know how to solve that with jsonata.