Is writing custom function possible in transform(array,func) in databricks sql?

[Jerry01]

This is the query I am trying to implement

Create function data_hide(data string)

Return if(is_member('groupName'),data,'****')

Table : my_table

Id Subject

​1. ['Eng','Bio']

2. ['Phy','Mat']

Select id, transform(Subject, x -> data_hide(x)) as new_data from my_table

The above select statement throwing me error​

[Anonymous]

@Naveena G​ :

The error in your SQL query may be caused by the fact that the transform function expects an array or map as its first argument, but you are passing a string instead. In this case, the Subject column appears to contain an array, but you are not specifying which element of the array to apply the data_hide function to. Assuming that you want to apply the data_hide function to each element of the Subject array separately, you can modify your query as follows:

SELECT id, transform(Subject, x -> data_hide(x)) AS new_data FROM my_table

However, if you only want to apply the data_hide function to the entire Subject array if the user is not a member of the groupName group, you can modify your query as follows:

SELECT id, IF(is_member('groupName'), Subject, transform(Subject, x -> data_hide(x))) AS new_data FROM my_table

This will return the original Subject array if the user is a member of the groupName group, otherwise it will apply the data_hide function to each element of the array.

[Anonymous]

Hi @Naveena G​ 

Thank you for posting your question in our community! We are happy to assist you.

To help us provide you with the most accurate information, could you please take a moment to review the responses and select the one that best answers your question?

This will also help other community members who may have similar questions in the future. Thank you for your participation and let us know if you need any further assistance!