The data information required by 1. is in the value field, which is in json format. First, you should extract the list information in this field.
Get_json_object (value,' $. htllist’)
2. The return value of 2.get _ JSON _ object is in string format, as follows.
There are' ['and']' before and after the string, and each {} contains the information of a single hotel, separated by',' before the {}.
4. In order to separate each {} in the future, you need to replace the delimiter',' with the delimiter' |' and' []' with''.
regexp _ replace(regexp _ replace(get _ JSON _ object(value，' $。 htllist ')，' }，'，' }\\| ')，' \\]|\\['，'')
5. Separate the strings obtained in the previous step according to "|" to obtain an array of each hotel information.
Side view decomposition (split(m.htllist,' \\|')) n as htlinfo
6. The 6.str _ to _ map function converts each element of the hotel information array into a map format, and obtains the ['hotelind'] information from it.
str _ to _ map(regexp _ replace(n . HTL info，' \\}|\\{| " '，'')，'，'，':')['hotelid']
7. The complete sql extraction is as follows
select str _ to _ map(regexp _ replace(n . HTL info，' \\}|\\{| " '，'')，'，'，':')['hotelid'] htlinfo，m.vid，m.sid，m.pvid，m.uid，m.cid
? from(select regexp _ replace(regexp _ replace(get _ JSON _ object(value，' $。 htllist ')，' }，'，' }\\| ')，' \\]|\\['，' ')htllist，vid，sid，pvid，uid，cid
? From table _xxx
Where d='20 18-08-08'
? And pagecode='condition 1'
? And key limit 100)m
? Side view decomposition (split(m.htllist,' \\|')) n as htlinfo
Where m.htllist is not empty.