Default value for a date field in the JSON_TABLE(json) function of SAP HANA

[iperez-sofos]

Greetings.

I think the title introduces a bit what the issue is. I describe it in detail below.

I am using the SAP HANA JSON_TABLE() function to query and extract data from a JSON text.Data that is then inserted into a table.

One of the data fields in the JSON text is a date, which is optional (that is, it might not be in the JSON).If the date does not exist in the JSON text, I must take (insert in the table) the current system date.I want to use an instruction like the following:

INSERT INTO MY_TAB (id, genBatch, stage, breed, gender,product, qty, edate, createTr)
     SELECT JT.id, JT.genBatch, JT.stage, JT.breed, JT.gender, JT.prod, TO_DOUBLE(JT.qty),
            JT.bdate, :txn
       FROM JSON_TABLE(:tData, '
                COLUMNS
                (
                    id NVARCHAR(36) PATH '$.id',
                    genBatch NVARCHAR(36) PATH '$.externalID',
                    breed NVARCHAR(36) PATH '$.breed',
                    gender NVARCHAR(80) PATH '$.gender',
                    stage NVARCHAR(36) PATH '$.stage',
                    prod NVARCHAR(36) PATH '$.product',
                    qty NVARCHAR(50) PATH '$.quatity',
                    bdate DATE PATH '$.date' DEFAULT CURRENT_DATE ON EMPTY
                )
            ) AS JT;

The previous code has a syntax error, which I suppose has to do with the fact that the SAP HANA SQL function CURRENT_DATE is only valid to invoke it in the SELECT and WHERE clauses.

My specific questions are:

Is it possible to do what I am looking for?

What option do I have to do it?

I have tried putting the value of the current date in a local variable and then using the value of that variable in the expression (DEFAULT :lv_currDate ON EMPTY), but it doesn't work.It seems to me that the expression with which the default value can be assigned to an empty field in the JSON_TABLE function must necessarily be a constant expression (although I really haven't found a documentation where they say it explicitly).

Note:I am working with SAP HANA Express Edition SPS4 on my local computer.

Thanks in advance for any answers and / or suggestions.

[Kai_Mueller · Accepted solution]

Hello,

you could use the normal SQL functions IFNULL, CASE or COALESCE:

create table json_data(dat nvarchar(5000));
insert into json_data values('{"id": 1, "var": 2, "opt": 3}');
insert into json_data values('{"id": 2, "var": 3}');

select 
	JT.id, 
	JT.var, 
	IFNULL(JT.opt, 100)
FROM JSON_TABLE (
	json_data.dat,
	'$'
	COLUMNS (
		id INT PATH '$.id',
		var INT PATH '$.var',
		opt INT PATH '$.opt'
	)
) JT

In regards to DEFAULT X ON EMPTY needs to be a constant as far as I know. So use IFNULL and it should work fine.

Best regards, Kai