extracting text from column using table transformer sql

[Holly Perry]

Hi! 

I have a table that is pulling data using JSON, and I've manipulated the columns using table transformer. I'm on a confluence server, not cloud. 

SELECT
(T1.'name')as 'Field Name',
(T1.'custom') as 'Custom Field?',
(T1.'id') as 'Custom ID',
(T1.'searchable') as 'Searchable Field?',
(T1.'schema') AS 'Field Schema'
From T1

one of the columns is returning the following (which was an expected return): 

{"type":"user","custom":"com.atlassian.jira.plugin.system.customfieldtypes:userpicker","customId":21400}

I would like to now be able to extract just "user" from this text, using SQL, but I'm running into errors when I attempt to add substring functions to my above sql. 

ideally I will make a column called "Field Type" and be able to put the extracted information into it. 

any help you could provide would be greatly appreciated! :) 

[Katerina Kovriga _Stiltsoft_ · Accepted solution]

Hi @Holly Perry ,

The issue is that your "type" has different length (for example, "user" vs "resolution"). Maybe instead of the SUBSTRING function it will be better to use the "split" operator.

Here is my example:

SELECT 'Col 1' AS 'Row number',
'Col 2'->split(":")->1 AS 'Type'
FROM (SELECT 'Col 1', 'Col 2'->split(",")->0 AS 'Col 2' FROM T*)

With the help of this query we split the cell contents by "," and leave the first part (see the internal SELECT). As a result we get {"type":"user", {"type":"string", etc.

The second step (see the external SELECT) splits our cell by ":" and we leave the second part.

Here is how the result table looks:

Hope this helps.

[Katerina Kovriga _Stiltsoft_]

I've modified the result query a little bit to get rid of the quotation marks (used the "slice" function to cut out extra symbols {"type":" in the beginning and " in the end of the string):

SELECT 'Col 1' AS 'Row number',
SUBSTRING('Col 2', 10, LENGTH('Col 2'))->slice(0, -1) AS 'Type'
FROM (SELECT 'Col 1', 'Col 2'->split(",")->0 AS 'Col 2' FROM T*)

Here is the result table:

[Holly Perry]

This is excellent! Thank you so so much!  is there a way to get rid of the "" around the text in the Type column? just curious. :) 

[Holly Perry]

lol just seeing your comment now. PERFECT!

[Holly Perry]

you are incredible, thank you so much!!

[Holly Perry]

one last question (sorry) is there a way to add back in my other columns with this sql? usually I can add columns by doing      SELECT T1.'Name'. etc - I'm just not seeing where I can put it into this. 

[Katerina Kovriga _Stiltsoft_]

Find the "Col 1" column - I select it in the internal SELECT and then rename it as 'Row number' in the external SELECT.

This column stands for your "name", "custom", etc. columns - just list them in the internal SELECT and then rename in the external SELECT.

But don't use the T1. prefix - here it can confuse the macro. Use plain column names as is shown in the example.

[Holly Perry]

a million thank yous!!! :) 

[Katerina Kovriga _Stiltsoft_]

Glad it helped! :)