Hive Sql Query To get Json Object from Json Array

Question

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.

sasi · Accepted Answer · 2020-12-04 03:34:17Z

2

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%'

edited Dec 4, 2020 at 3:34

answered May 7, 2020 at 17:10

sasi

3644 silver badges16 bronze badges

Sign up to request clarification or add additional context in comments.

Collectives™ on Stack Overflow

Hive Sql Query To get Json Object from Json Array

1 Answer 1

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related