qlik sense extract the value from the json

迷南。 2022-12-07 12:49 180阅读 0赞

## 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.  
![在这里插入图片描述][20200916095752880.png_pic_center]  
我们连接数据库查询日志表,然后从日志表得json中解析出timezone.

## the json structure in below:   ![在这里插入图片描述][20200916100236886.png_pic_center]   Soluction ##

1.`PurgeChar(log,'"')` ,remove the special characters,移除特殊符号  
![在这里插入图片描述][20200916100513390.png_pic_center]

2.`SubField(PurgeChar(log,'"'),'timezone:',2)` ,substring by timezone and get the part 2 ,根据timezone来分割并获取第二部分  
![在这里插入图片描述][20200916100610253.png_pic_center]

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:  
![在这里插入图片描述][2020091610092527.png_pic_center]

[20200916095752880.png_pic_center]: /images/20221123/e955c23b90bd4ad597d9be83900e6b78.png
[20200916100236886.png_pic_center]: /images/20221123/5dd72c12680348bc9c13ddf0002613b2.png
[20200916100513390.png_pic_center]: /images/20221123/26ad87c348ab4463acf837815055a500.png
[20200916100610253.png_pic_center]: /images/20221123/3624a75d3f0c428995c075cec8fca4be.png
[2020091610092527.png_pic_center]: /images/20221123/aec900ae68164147b8b3413cbbb78055.png