Вопрос или проблема
Я пытаюсь разобрать JSON-ответ из отчета CIBIL и отобразить поле paymentHistory в Bold BI в виде табличной визуализации. Цель состоит в том, чтобы показать “Количество дней просрочки/Классификация актива” за 36-месячный период (слева направо). Ниже приведен пример формата, который я хочу достичь:
ДНЕЙ ПРОСРОЧКИ:
000 000 055 024 000 000 000 000 000 000 000 000 000 000 024 024 084 113 113 114 113 116 115 115 116 085 055 024 000 000
ДАТА (ММ-ГГ):
10-24 09-24 08-24 07-24 06-24 05-24 04-24 03-24 02-24 01-24 12-23 11-23 10-23 09-23 08-23 07-23 06-23 05-23 04-23 03-23 02-23 01-23 12-22 11-22 10-22 09-22 08-22 07-22 06-22 05-22
Вот фрагмент JSON-данных:
{
"index": "T004",
"memberShortName": "NOT DISCLOSED",
"accountType": "05",
"dateOpened": "23052022",
"paymentHistory": "000000055024000000000000000000000000000000024024084113113114113116115115116085055024000000",
"paymentStartDate": "01102024",
"paymentEndDate": "01052022"
}
Я хочу извлечь строку paymentHistory и отформатировать ее для отображения “Дней просрочки” вместе с соответствующими месяцами (ММ-ГГ) в Bold BI. paymentHistory — это строка, где каждая группа из трех цифр представляет количество дней просрочки за данный месяц.
Что я пробовал
С ограниченными выражениями, доступными в Bold BI, я использовал выражение SUBSTRING для разбивки paymentHistory на 3-значные блоки, представляющие дни просрочки. Однако я не мог понять, как отобразить месяцы рядом с значениями. Это затрудняло правильное выравнивание “Дней просрочки” с соответствующими месяцами.
Что я ожидал
Я ожидал динамически извлекать строку paymentHistory из данных JSON и разбивать ее на 3-значные блоки, каждый из которых представляет “Дни просрочки” для каждого месяца. Я хотел, чтобы эти значения автоматически выравнивались с соответствующими месяцами (ММ-ГГ) в табличном формате в Bold BI. В частности, я надеялся увидеть гибкое решение, которое могло бы адаптироваться к различной длине paymentHistory без необходимости вручную настраивать код для различных наборов данных. Идеально, это позволило бы мне легко визуализировать данные о платежной истории в понятной и организованной манере.
Вы можете начать с этого:
with data (js) as (
select q'~{
"index": "T004",
"memberShortName": "NOT DISCLOSED",
"accountType": "05",
"dateOpened": "23052022",
"paymentHistory": "000000055024000000000000000000000000000000024024084113113114113116115115116085055024000000",
"paymentStartDate": "01102024",
"paymentEndDate": "01052022"
}~' from dual
),
payments(paymentHistory, paymentStartDate) as (
select paymentHistory, to_date(paymentStartDate, 'ddmmyyyy') as paymentStartDate
from data d,
json_table(
d.js
columns (
paymentHistory varchar2(4000) path '$.paymentHistory',
paymentStartDate varchar2(8) path '$.paymentStartDate'
)
)
)
select
substr(paymentHistory, 3*(level-1), 3) as days_past_due,
to_char(add_months(paymentStartDate, -(level-1)),'MM-YY') as "DATE"
from payments
connect by 3*(level-1) < length(paymentHistory)
;
000 10-24
000 09-24
005 08-24
502 07-24
...
Ответ или решение
Как Парсить и Отображать Историю Платежей CIBIL в Bold BI с JSON
Ваша задача заключается в том, чтобы извлечь поле paymentHistory из JSON-ответа отчета CIBIL и отобразить его в виде табличной визуализации в Bold BI. Рассмотрим, как эффективно выполнить эту задачу, используя представленную информацию.
Шаг 1: Понимание Формата JSON
Ваш JSON-ответ выглядит так:
{
"index": "T004",
"memberShortName": "NOT DISCLOSED",
"accountType": "05",
"dateOpened": "23052022",
"paymentHistory": "000000055024000000000000000000000000000000024024084113113114113116115115116085055024000000",
"paymentStartDate": "01102024",
"paymentEndDate": "01052022"
}Обратите внимание на поле paymentHistory, которое является строкой длиной 108 символов. Каждые три цифры в этой строке представляют количество дней просрочки (Days Past Due) за один месяц.
Шаг 2: Подготовка SQL-запроса для Извлечения Данных
Чтобы извлечь необходимые данные и отобразить их в Bold BI, вы можете использовать следующий SQL-запрос. В этом запросе мы будем использовать процедуру JSON_TABLE для извлечения данных из JSON, а также функцию SUBSTR для разделения строки paymentHistory на подстроки.
WITH data (js) AS (
SELECT q'~{
"index": "T004",
"memberShortName": "NOT DISCLOSED",
"accountType": "05",
"dateOpened": "23052022",
"paymentHistory": "000000055024000000000000000000000000000000024024084113113114113116115115116085055024000000",
"paymentStartDate": "01102024",
"paymentEndDate": "01052022"
}~' FROM dual
),
payments(paymentHistory, paymentStartDate) AS (
SELECT paymentHistory, TO_DATE(paymentStartDate, 'ddmmyyyy') AS paymentStartDate
FROM data d,
JSON_TABLE(
d.js
COLUMNS (
paymentHistory VARCHAR2(4000) PATH '$.paymentHistory',
paymentStartDate VARCHAR2(8) PATH '$.paymentStartDate'
)
)
)
SELECT
SUBSTR(paymentHistory, 3*(LEVEL-1)+1, 3) AS days_past_due,
TO_CHAR(ADD_MONTHS(paymentStartDate, -(LEVEL-1)), 'MM-YY') AS "DATE"
FROM payments
CONNECT BY 3*(LEVEL-1) < LENGTH(paymentHistory);Описание Запроса
-
Первый CTE (
data): Содержит JSON-данные. -
Второй CTE (
payments): ИспользуетJSON_TABLEдля извлечения полейpaymentHistoryиpaymentStartDateиз JSON. -
Основной запрос:
SUBSTR(paymentHistory, 3*(LEVEL-1)+1, 3): Извлекает три символа для каждого месяца, представляя "Дни Просрочки".TO_CHAR(ADD_MONTHS(paymentStartDate, -(LEVEL-1)), 'MM-YY'): Генерирует соответствующую дату в формате ММ-ГГ.CONNECT BYпозволяет создать итерацию по каждому месяцу, пока длинаpaymentHistoryэто позволяет.
Шаг 3: Отображение Результатов в Bold BI
Для того чтобы интегрировать этот результат в Bold BI в виде табличной визуализации:
- Используйте полученные данные в качестве источника данных для построения таблицы.
- Отобразите столбец
days_past_dueрядом со столбцомDATEдля правильного визуального представления. - Обеспечьте, чтобы форматирование соответствовало вашим ожиданиям.
Заключение
С помощью описанных шагов вы сможете успешно выполнить парсинг и отображение истории платежей CIBIL в Bold BI, создавая табличную визуализацию, где "Дни Просрочки" будут корректно сопоставлены с соответствующими месяцами. Это позволит вам наглядно представлять финансовую информацию, необходимую для анализа платежной истории.