SAP HANA SQL can't reference column from outer table inside open_json function

Question

I can't find any information if I can reference outside table column while trying to convert JSON data in a table format.

An example:

-- create temp table for data

create local temporary table #test("StorageId" INT, "Items" nvarchar(200));

-- insert 1 test data

insert into #test("StorageId", "Items")
values(1, '[22,33]');

-- query used for converting the json data to table where im passing the json column from the outside table

SELECT e."StorageId"
FROM #test AS e
where 22 in (
        select "ItemId" from JSON_TABLE(e."Items", '$[*]' COLUMNS ("ItemId" INT PATH '$'))
    )

And this query returns error:

(dberror) [259]: invalid table name: table does not exist > E:

If I put the array string instead of the outside table column then the query executes:

select e."StorageId"
from #test AS e
where 22 in (
        select "ItemId" from JSON_TABLE('[22,33]', '$[*]' COLUMNS ("ItemId" INT PATH '$'))
    )

Mathias Kemeter · Accepted Answer · 2025-01-16 14:29:00Z

1

Seems like the alias e is not know in the inner scope. You can instead use the full table reference:

SELECT e."StorageId"
FROM #test AS e
where 22 in (
        select "ItemId" from JSON_TABLE(#test."Items", '$[*]' COLUMNS ("ItemId" INT PATH '$'))
    )

If you are working with SAP HANA Cloud, I'd encourage to take a look at its JSON Document Store as it offers performant filtering by array elements even for large data volumes.

answered Jan 16 at 14:29

Mathias Kemeter

1,1915 silver badges15 bronze badges

Sign up to request clarification or add additional context in comments.

Collectives™ on Stack Overflow

SAP HANA SQL can't reference column from outer table inside open_json function

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related