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}

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.