Skip to Main Content

SQL & PL/SQL

Announcement

For appeals, questions and feedback about Oracle Forums, please email oracle-forums-moderators_us@oracle.com. Technical questions should be asked in the appropriate category. Thank you!

Select data from JSON object

Pavel_pMay 14 2021

Hello,
I have the following JSON:

{
	"_nodes": {
		"total": 25,
		"successful": 25,
		"failed": 0
	},
	"cluster_name": "testcluster",
	"nodes": {
		"-7ECgmInS0yL75s8n10Bmw": {
			"timestamp": 1620895198060,
			"name": "node1",
			"transport_address": "10.6.142.134:9300",
			"host": "host1",
			"ip": "10.6.142.134:9300",
			"roles": [
				"data",
				"transform"
			],
			"attributes": {
				"disk": "ssd",
				"xpack.installed": "true",
				"zone": "MYZONE",
				"transform.node": "true"
			},
			"indices": {
				"search": {
					"open_contexts": 5,
					"query_total": 10
				}
			}
		},
		"TLIt5CKsSZKJ7HImhSVJSw": {
			"timestamp": 1620895198062,
			"name": "node2",
			"transport_address": "10.6.142.233:9300",
			"host": "host2",
			"ip": "10.6.142.233:9300",
			"roles": [
				"data",
				"transform"
			],
			"attributes": {
				"disk": "hdd",
				"xpack.installed": "true",
				"zone": "MYZONE",
				"transform.node": "true"
			},
			"indices": {
				"search": {
					"open_contexts": 15,
					"query_total": 20
				}
			}
		}
	}
}

and I would like to select values of open_contexts and query total from all nodes. The problem is that there is n nodes and I dont know the node namesĀ (in this case -7ECgmInS0yL75s8n10Bmw and TLIt5CKsSZKJ7HImhSVJSw) in advance.
Expected output would be

open_contexts           query_total
5                             10
15                            20

Any ideas would be highly appreciated.
Regards,
Pavel

This post has been answered by Paulzip on May 14 2021
Jump to Answer
Comments
Post Details
Added on May 14 2021
15 comments
1,082 views