从Presto的JSON列获取特定值 [英] Get specific values from JSON column in Presto
问题描述
我有一个带有JSON列 points
的表,其中的行之一为:
I have a table with a JSON column points
with one of the rows as:
{"0": 0.2, "1": 1.2, "2": 0.5, "15": 1.2, "20": 0.7}
我想获取键 1
和 20的值
并将其作为别名存储在查询中,例如 first
和 second
。到目前为止,我所做的是:
I want to get the values for keys "1"
and "20"
and store them as an alias like first
and second
in a query. What I've done till now is:
从奖励中选择积分,k,v交叉联接UNNEST(SPLIT_TO_MAP(points,',', ':'))AS m(k,v)其中name ='John'
但是此查询为我提供了k的所有行,v。如何仅选择与 1和 20相对应的两个值?
But this query gives me all the rows of k, v. How do I select only those two values corresponding to "1" and "20"?
推荐答案
JSON_EXTRACT_SCALAR达到了目的。
JSON_EXTRACT_SCALAR did the trick.
JSON_EXTRACT_SCALAR(points,'$ [ 1]')AS first_value
JSON_EXTRACT_SCALAR(points,'$ [ 20]')AS second_value
这篇关于从Presto的JSON列获取特定值的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持IT屋!