Postgres 15

By using db<>fiddle, you agree to license everything you submit by Creative Commons CC0.
SELECT jsonb_path_query(payload.content, '$.node1.node2.node3.node4[*]')->>'attr1'
FROM (VALUES ('{"node1": {
    "node2": {
        "node3": {
            "node4": {
                "attr1": "7d181b05-9c9b-4759-9368-aa7a38b0dc69",
                "attr2": "S1",
                "UserID": "WebServices",
                "attr3": "S&P 500*",
                "attr4": "EI",
                "attr5": "0"
            }
        }
    }
}}' :: jsonb)
           , ('{"node1": {
    "node2": {
        "node3": {
            "node4": [
                {
                    "attr1": "7d181b05-9c9b-4759-9368-aa7a38b0dc69",
                    "attr2": "S1",
                    "UserID": "WebServices",
                    "attr3": "S&P 500*",
                    "attr4": "EI",
                    "attr5": "0"
                },
                {
                    "attr1": "7d181b05-9c9b-4759-9368-aa7a38b0dc69",
                    "attr2": "S1",
                    "UserID": "WebServices",
                    "attr3": "S&P 500*",
                    "attr4": "EI",
                    "attr5": "0"
                }
            ]
        }
    }
}}' :: jsonb)) AS payload(content)


?column?


7d181b05-9c9b-4759-9368-aa7a38b0dc69

7d181b05-9c9b-4759-9368-aa7a38b0dc69

7d181b05-9c9b-4759-9368-aa7a38b0dc69



SELECT 3


select ('{"a":1}' ::jsonb)->'a'


?column?


1



SELECT 1


SELECT jsonb_path_query(payload.content, 'strict $.node1.node2.node3.node4[*]')->>'attr1'
FROM (VALUES ('{"node1": {
    "node2": {
        "node3": {
            "node4": {
                "attr1": "7d181b05-9c9b-4759-9368-aa7a38b0dc69",
                "attr2": "S1",
                "UserID": "WebServices",
                "attr3": "S&P 500*",
                "attr4": "EI",
                "attr5": "0"
            }
        }
    }
}}' :: jsonb)
           , ('{"node1": {
    "node2": {
        "node3": {
            "node4": [
                {
                    "attr1": "7d181b05-9c9b-4759-9368-aa7a38b0dc69",
                    "attr2": "S1",
                    "UserID": "WebServices",
                    "attr3": "S&P 500*",
                    "attr4": "EI",
                    "attr5": "0"
                },
                {
                    "attr1": "7d181b05-9c9b-4759-9368-aa7a38b0dc69",
                    "attr2": "S1",
                    "UserID": "WebServices",
                    "attr3": "S&P 500*",
                    "attr4": "EI",
                    "attr5": "0"
                }
            ]
        }
    }
}}' :: jsonb)) AS payload(content)

ERROR:  jsonpath wildcard array accessor can only be applied to an array