在我的hive表 "ticket_full "中，我有一列名为 "service_id "的json类型的列，我想用3列来提取，是这样的。
[{"position":"1","typeid":"ROUTNAME","value":"PWAW13197"},{"position":"2","typeid":"CDCNAME","value":null},{"position":"3","typeid":"SVCNAME","value":"Business"},{"position":"4","typeid":"USID","value":"FI021MLQE4"}]
[{"position":"1","typeid":"ROUTNAME","value":"KHLA30076"},{"position":"2","typeid":"CDCNAME","value":"eff-e-rjh-sw-cs2"},{"position":"3","typeid":"SVCNAME","value":"Managed LAN"},{"position":"4","typeid":"USID","value":"SA00BNGH0E"}]
[{"position":"1","typeid":"NUMLIAPTT","value":"0492212984"},{"position":"2","typeid":null,"value":null},{"position":"3","typeid":null,"value":null},{"position":"4","typeid":null,"value":null}]我使用了下面的代码。
SELECT get_json_object(single_json_table.identifiant_produit, '$.position') AS position,
get_json_object(single_json_table.identifiant_produit, '$.typeid') AS typeid,
get_json_object(single_json_table.identifiant_produit, '$.value') AS value
FROM
(SELECT explode(split(regexp_replace(substr(serviceid, 2, length(serviceid)-2),
'"},\\{"', '"},,,,{"'), ',,,,') ) as identifiant_produit
FROM ticket_full) single_json_table它的工作原理是，但每当有一个值为null时，它就会忽略后面的内容，并转到下一个字段：例如。
有谁知道如何解决这个问题呢？
1条答案
按热度按时间puruo6ea1#
这是因为null没有双引号，你把这个
'"},\{"'``替换成了这个'"},,,,{"'`。试着去掉regex模式和替换字符串中的}前的双引号，那么它也可以使用引号和空值。