Come for the products,
stay for the community

The Atlassian Community can help you and your team get more value out of Atlassian products and practices.

Atlassian Community about banner
4,295,867
Community Members
 
Community Events
165
Community Groups

extracting text from column using table transformer sql

Edited

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! :) 

Screen Shot 2022-06-07 at 11.55.38 AM.png

 

1 answer

1 accepted

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:

Tue 4-1.png

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:

Tue 4-2.png

Hope this helps.

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:

Tue 4-3.png

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. :) 

lol just seeing your comment now. PERFECT!

you are incredible, thank you so much!!

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. 

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.

a million thank yous!!! :) 

Suggest an answer

Log in or Sign up to answer
DEPLOYMENT TYPE
CLOUD
TAGS
Community showcase
Published in Confluence

Confluence: Where work and wellness meet

Feeling overwhelmed by the demands of work and life? With a 25% increase in the prevalence of anxiety and depression worldwide during the pandemic, for most of us, it’s a resounding yes . 🙋‍♀️ ...

974 views 15 27
Read article

Community Events

Connect with like-minded Atlassian users at free events near you!

Find an event

Connect with like-minded Atlassian users at free events near you!

Unfortunately there are no Community Events near you at the moment.

Host an event

You're one step closer to meeting fellow Atlassian users at your local event. Learn more about Community Events

Events near you