BI-6230

jira_tmp_1.sql

with mock as (
    select
-- a piece of real data:
'{"fields":{"customfield_10216":{"version":1,"type":"doc","content":[{"type":"paragraph","content":[{"type":"text","text":"Questions answered."},{"type":"hardBreak"},{"type":"text","text":"For GA4 customer have to use a difference granularity on free form report to match data with platform."},{"type":"hardBreak"},{"type":"text","text":"For Instagram as customer is using a lifetime report type they need to use the created_at_datetime field to find specific date range data."}]}]}}}'
    as _airbyte_data
)

, json_extract as (
select

 	JSONExtractString(_airbyte_data, 'fields', 'customfield_10216', 'content', 1, 'type')    as resolution_summary_type
,   JSONExtract(_airbyte_data, 'fields', 'customfield_10216', 'Nullable(String)')
                    
-- According to the logic we have now we take only the first row from the paragraph
-- and all content from bullet lists (all rows):
                      
,           if(
                resolution_summary_type = 'paragraph',
                JSONExtract(
                    _airbyte_data, 'fields', 'customfield_10216',
                        'content', 1, 'content', 1, 'text', 'Nullable(String)'
                ),
                null
            )                                                                                as resolution_summary_text		-- Must be renamed to "resolution_summary_paragraph_first_record"
            
,           if(
                resolution_summary_type = 'bulletList',
                JSONExtractArrayRaw(
                    _airbyte_data, 'fields', 'customfield_10216',
                    'content', 1, 'content'
                ),
                emptyArrayString()
            )                                                                                as resolution_summary_json_array
            
,           arrayMap(
                x -> JSONExtract(x, 'content', 1, 'content', 1, 'text', 'Nullable(String)'),
                resolution_summary_json_array
            )                                                                                as resolution_summary_array  -- Must be renamed in to "resolution_summary_first_record_array"

            ,arrayStringConcat(resolution_summary_array, '\n')                               as resolution_summary_array_to_string 

            ,nullIf(resolution_summary_array_to_string, '')                                  as resolution_summary_array_to_string_to_nullable
            
        -- New logic:
        -- To generate a concatenated list of rows for complete PARAGRAPH summary
        -- we need this extra field:
,           JSONExtractArrayRaw(
                    _airbyte_data, 'fields', 'customfield_10216',
                    'content', 1, 'content'
            ) 																			    as resolution_summary_json_array_from_paragraph
                
			,arrayMap(
                x -> JSONExtract(x, 'text', 'Nullable(String)'),
                resolution_summary_json_array_from_paragraph
            )                                                                               as resolution_summary_array_from_paragraph
            
            ,arrayStringConcat(resolution_summary_array_from_paragraph, '\n')               as resolution_summary_array_from_paragraph_to_string 

            ,nullIf(resolution_summary_array_to_string, '')                                 as resolution_summary_array_from_paragraph_to_string_to_nullable

            from mock
)

select

    coalesce(
        resolution_summary_array_from_paragraph_to_string                				-- New logic extra bit
        ,resolution_summary_text
        ,resolution_summary_array_to_string_to_nullable
    )                                                                               as `Resolution Summary`

from json_extract