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

Spring

ShilpaKulkarni

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