HANA DB usage of JSON embedded functions

[r_nikolov]

Hello all, 

I see that there are other questions here about HANA, pretty old but still decided to ask. 

I am trying to query some data and have a NCLOB column with a JSON inside. I can get a specific field using JSON_VALUE function.

Why I cannot use JSON_VALUE in a WHERE clause, doesn't it work exactly the same as a normal filtering, gets some column, check if matches a value, just a little bit more complicated etc. parsing and getting a specific field from a column with JSON inside.

[RobertWaywell]

Take a look at the JSON_TABLE function. It will present the result of the function as a table structure which can then be referenced as a table in other queries: JSON_TABLE Function (JSON)

[r_nikolov]

But still, would that help filtering? I would like to fetch 3 fields from the JSON inside the NCLOB and use it in a WHERE statement, but I don't want to fetch and parse everything on source code level. Is that possible in a simple manner?

[RobertWaywell]

If you look at the Example section (https://help.sap.com/docs/hana-cloud-database/sap-hana-cloud-sap-hana-database-sql-reference-guide/j...) it shows an example of a table that contains a JSON structure for LineItems. The full LineItems structure contains Item, Part, and Quantity values with Part being a nested structure containing Description, Unit Price and UPC code. The example SQL query provided shows how to select just the Item Number and UPC Code using the JSON_TABLE function. So if you only want to fetch 3 fields from the JSON you would write the appropriate SQL query to select those 3 fields and just return those 3 fields to the application.