How to use UDFs without actually writing one

exa-Franz
Team Exasol
Team Exasol

This is about hijacking the JSON functions in Exasol 😉

Assume you have a comma-separated list of values and want to unnest them.

What do you think of this, let me know.

with tmp as (values('1,2,3'),('4,5,6') as t(x))
SELECT 
    JSON_EXTRACT( 
'['||x||']'
,
'$#'
) EMITS(i INT) from tmp;

Have a good weekend everybody!

5 REPLIES 5

exa-Chris
Community Manager
Community Manager

Thank you @exa-Franz can we turn this in a category? Friday Tips by Franz and Friends? 

exaChris_0-1630067225739.png

 

Connecting Customers, Partners, Prospects and Exasolians is my passion. Apart from that I cycle, listen to music, and try to understand what all those technical discussions really mean...

mwellbro
Xpert

Please don´t call it "Franz and Friends"....kind of reminds me of a TV format I´m not particularly fond of 😜

Have a great weekend y´all , and thanks for the info @exa-Franz !

exa-Franz
Team Exasol
Team Exasol

I also recommend a different name 😉

@exa-MathiasHo once created that post https://community.exasol.com/t5/discussion-forum/what-s-the-best-query-trick-you-are-using/m-p/3541

Maybe you could call a potential series "neat little tricks" or something like that. Or community members just create a post once they find something interesting, doesn't happen every Friday, at least not to me 😉

Good start into the week everyone...

exa-Chris
Community Manager
Community Manager

Thats a good Idea, and with such Xperts like Malte and you. Its not only about the NEW things. Its more about the little things you do everyday, that a new starter would not know about.

Connecting Customers, Partners, Prospects and Exasolians is my passion. Apart from that I cycle, listen to music, and try to understand what all those technical discussions really mean...

exa-Franz
Team Exasol
Team Exasol

An extended version for the following question I was asked recently:

From something like this:

Column1 

Column2

A,B,C

Hello

 

Into this:

Column1 

Column2

A

Hello

B

Hello

C

Hello

 

Following changes to my original post:

  1. We are dealing with strings here, to make it a json compatible array, every string has to be quoted, that is why you have to add the "replace" sql function.
  2. There is an additional column, which is replicated automatically, and for this column there is no need to process it through the JSON_EXTRACT function (col2).
-- this is your test “table”, have a look at it
values(('A,B,C'),'Hello'),(('D,E,F'),'Bye') as t(col1,col2);
 
-- some sql and json magic
with tmp as(
        values(('A,B,C'),'Hello'),(('D,E,F'),'Bye') as t(col1,col2)
)
SELECT 
    JSON_EXTRACT( 
        '["'||replace(col1,',','","')||'"]'
        ,
        '$#'
        )
        EMITS(col1 varchar (1)),col2 from tmp;

 Maybe that helps someone 😉 Have a good start into the week!