hivehiveqlhive-query

Is there a Hiveql function using which we can pull records from a table where a JSON type column has a specific value for a key?

I'm looking to get count of records in which a column(type) of json type has certain key:value in table named product_type.

_______________________________________________________
id  |   product |            type                     |

1    | product_1  | {"costly": true, "l_type": true}  |
2    | product_2  | {"costly": false, "l_type": true} |
3    | product_3  | {"costly": false, "l_type": true} |
4    | product_4  | {"costly": false, "l_type": true} |
_______________________________________________________

Something like-

select count(id) from product_table where type has {"costly": false}

What I have tried:

select count(*) from product_table where type LIKE '%"costly": false%'

-> which is not yielding any results.

Expecting to get: 3 ( as there are 3 records that has value as false in type column for the key costly.)

Solution

  • I got it resolved:

    select count(*) from product_table where get_json_object(type,$.costly) is true;