Skip to Content
0
Nov 09, 2020 at 12:23 PM

JSON_TABLE Nested array

209 Views

Hi.

Can you please help me get result as this shown below but with correct values in Attribute_value column

How should I write HANA script to get value of array without named column

My script looks like this

SELECT *
FROM JSON_TABLE("ALTADDONS"."_ATB_XDTW_OITM_TEMP_ADD"."B", '$.items[*]'
COLUMNS
(
id int PATH '$.id',
    NESTED PATH '$.custom_attributes'
COLUMNS
      (
       attribute_code NVARCHAR(254) PATH '$.attribute_code',
       NESTED PATH '$.value'
       COLUMNS
       (
       attribute_value NVARCHAR(254) PATH '$.*'
       )              
       )       
)
) AS JT;

I don't know what I should put in here attribute_value NVARCHAR(254) PATH '$.*'

{
    "items": [
        {
            "id": 1,
            "sku": "product_dynamic_1",
            "name": "ArgentyƄski stek antrykot Black Angus",
            "price": 35,
            "type_id": "simple",
            "weight": 1,
            "custom_attributes": [
                {
                    "attribute_code": "category_ids",
                    "value": [
                        "282",
                        "301",
                        "303"
                    ]
                },
                {
                    "attribute_code": "has_options",
                    "value": "0"
                }
            ]
        }
    ]
}

Attachments

tqyle.png (7.0 kB)