CODEWITHSUNDEEP
jsonhivehiveql
Vijaya Seetharaman
Vijaya Seetharaman

Reputation: 181

Hive Sql Query To get Json Object from Json Array

I have a json inside 'content' column in the following format:

  {  "identifier": [
        {
          "type": {
            "coding": [
              {
                "code": "MRN",
              }
            ]
          },
          "value": "181"
        },
        {
          "type": {
            "coding": [
              {
                "code": "PID",
              }
            ]
          },
          "value": "5d3669b0"
        },
        {
          "type": {
            "coding": [
              {
                "code": "IPN",
              }
            ]
          },
          "value": "41806"
        }
      ]}

I have to run an hive query to get the "value" of the code which is equal to "MRN". I have written the following query but its not giving the value as expected:

select get_json_object(content,'$.identifier.value')as Mrn from Doctor where get_json_object(content,'$.identifier.type.coding.code') like '%MRN%'

I dont want to give particular array position like:

select get_json_object(content,'$.identifier[0].value')as Mrn from Doctor where get_json_object(content,'$.identifier[0].type.coding.code') like '%MRN%'

As the json gets created randomly and the position is not fixed always.

Upvotes: 1

Views: 8022

Answers (1)

sasi
sasi

Reputation: 364

Give [ * ] to avoid giving position.

select get_json_object(content,'$.identifier[*].value')as Mrn from Doctor where get_json_object(content,'$.identifier[*].type.coding.code') like '%MRN%'

Upvotes: 2

Related Questions