We have a very big dataset. And I need to get all the values that are mapping from the source attributes to normalized attributes in my json. The relation between normalised and source is that if the source_key in normalized values is same like the source_attributes keys then we should return all their names.

My input is somewhat like this:

  "source_attributes": {
    "desirability": {
      "values": [
        {
          "value": "000000002"
        }
      ]
    },
    "isbn-13": {
      "values": [
        {
          "value": "9781934568392"
        }
      ]
    }
  },
  "normalized": {
    "isbn-13": [
      {
        "properties": {
          "attributeId": "85984",
          "multiselect": "N",
          "domain": "pcs",
          "display_attribute_name": "ISBN-13",
          "taxonomy_version": "urn:taxonomy:pcs2.0",
          "attributeName": "ISBN-13"
        },
        "values": [
          {
            "display_attr_name": "ISBN-13",
            "locale": "en_US",
            "value": "9781934568392",
            "isPrimary": "true",
            "source_value": "9781934568392",
            "source_key": "isbn-13"
          }
        ]
      }
    ]
  }
}

My desired output is something like this:

The query I wrote fetches all the values from the data that has source attributes and normalized but how do I compare the values as the structurally both the elements are different?

SELECT json_extract(element_json, '$.source_attributes') as source_att, json_extract(element_json, '$.normalized') as normal
FROM `data`
where json_value(element_json,'$.source')='TEST'
limit 1000;