How to fetch the value from database column which is in JSON format

Asked on May 08, 2019
Hi,
I want to fetch the value from table column which is in JSON format.
For example I have table called json_tree which has following 2 columns:
- id
- location_tree
location_tree column contains data in JSON Format.
I have following JSON values in location_tree column.
{"text": "India","value": 1,"locationCode": "001","children": [{"text": "Karnataka","value": 37,"locationCode": "EX01","disabled": "false","children": [{"text": "Hubli","value": 38,"disabled": "false","locationCode": "UBL01","children": [{"text": "Vidyanagar","value": 43,"disabled": "false","locationCode": "VID01","children": []}]},{"text": "Bangalore","value": 40,"disabled": "false","locationCode": "BANG01","children": [{"text": "JayNagar","value": 48,"disabled": "false","locationCode": "JAY000","children": [{"text": "Vidyagiri","value": 56,"disabled": "false","locationCode": "VID001","children": []}]}]},{"text": "Mysore","value": 57,"disabled": "false","locationCode": "CG002","children": []}]}]}
From this JSON I want to pass one location name and fetch the parent (grand parents)and children
(grand children) of the passed location.
For Example: If I pass Banglore location , then I should get following JSON response:
{"text": "India","value": 1,"locationCode": "001","children": [{"text": "Karnataka","value": 37,"locationCode": "EX01","disabled": "false","children": [{"text": "Bangalore","value": 40,"disabled": "false","locationCode": "BANG01","children": [{"text": "JayNagar","value": 48,"disabled": "false","locationCode": "JAY000","children": [{"text": "Vidyagiri","value": 56,"disabled": "false","locationCode": "VID001","children": []}]}]}]}]}As response It should give parents(grand parents) and children (grand children) of Bangalore location.How to achieve this?Can any one please provide solution for this?Thanks & Regards,Shilpa Kulkarni