json - Json_tuple说 hive 中的无效json

我正在解析存储为表中行的json

它解析具有简单字符串但不包含文件路径的json行

例如:

{
    "CustomerID": "C101",
    "BillLocation": "C:\Customer\Files\C101\1.txt",
    "CustomerLocation": "NY",
    "Company":"XYZ"
}

我尝试了在线json验证程序，它在帐单位置出现了错误，但是当将\附加到所有显示\的位置时，它便得到了验证，
像这样C:\\Customer\\Files\C101\\1.txt

select a.CustomerID,
a.BillLocation,
a.CustomerLocation,
Company from db.jsontable b
LATERAL VIEW json_tuple(b.json,'CustomerId','BillLocation','CustomerLocation','CustomerLocation') a as CustomerId,BillLocation,CustomerLocation,CustomerLocation

最佳答案

select  a.*

from    jsontable b

        lateral view

        json_tuple
        (
            regexp_replace(b.json,'\\\\','\\\\\\\\')
           ,'CustomerID','BillLocation','CustomerLocation','CustomerLocation','Company'
        ) a as CustomerId,BillLocation,CustomerLocation,CustomerLocation,Company

+------------+------------------------------+------------------+---------+
| customerid |         billlocation         | customerlocation | company |
+------------+------------------------------+------------------+---------+
| C101       | C:\Customer\Files\C101\1.txt | NY               | XYZ     |
+------------+------------------------------+------------------+---------+