Json_tuple 表示 hive 中的无效 json

Question

我正在解析存储为表中的行的 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

Answer 1

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     |
+------------+------------------------------+------------------+---------+