qlik sense extract the value from the json

Background

qlik sense doesn’t provide a function to quickly extract the value from the json ,so we have to use many origin functions to get value from the json.
QLIK SENSE本身并没有提供一个JSON解析方法可以直接从JSON中提取值,所以必须用原生方法进行拼凑来提取值.

Requirement

Given that we connect to pgsql for querying the log,
We want to extract the timezone value from the json,
So that we can use the timezone as a field in table.

我们连接数据库查询日志表,然后从日志表得json中解析出timezone.

the json structure in below:

Soluction

1.PurgeChar(log,'"') ,remove the special characters,移除特殊符号

2.SubField(PurgeChar(log,'"'),'timezone:',2) ,substring by timezone and get the part 2 ,根据timezone来分割并获取第二部分

3.SubField(below_value,',',1) ,substring by below value and then just keep the part 1, 根据以上得值再度分割并且只保留第一部分.

the final expression:
=SubField(SubField(PurgeChar(log,'"'),'timezone:',2),',',1)

verify the expression on table: