so41473305

View All Public Scripts Login to Run Script

Script Name so41473305
Visibility Unlisted - anyone with the share link can access
Description JSON test for StackOverflow http://stackoverflow.com/q/41473305/266304
Area SQL General / JSON
Contributor alex.poole@netcracker.com
Created Thursday January 05, 2017

Statement 1

DECLARE
           l_json clob;
           l_var varchar2(90);
BEGIN
    l_json := '  {
"first": {
	"private": false,
	"html_url": "https://github.com/mralexgray/Alfred-Google-Translate",
	"description": "Extension for Alfred that will do a Google translate for you",
	"fork": true,
	"url": "https://api.github.com/repos/mralexgray/Alfred-Google-Translate",
	"forks_url": "https://api.github.com/repos/mralexgray/Alfred-Google-Translate/forks",
	"keys_url": "https://api.github.com/repos/mralexgray/Alfred-Google-Translate/keys{/key_id}",
	"collaborators_url": "https://api.github.com/repos/mralexgray/Alfred-Google-Translate/collaborators{/collaborator}",
	"teams_url": "https://api.github.com/repos/mralexgray/Alfred-Google-Translate/teams",
	"hooks_url": "https://api.github.com/repos/mralexgray/Alfred-Google-Translate/hooks",
	"issue_events_url": "https://api.github.com/repos/mralexgray/Alfred-Google-Translate/issues/events{/number}",
	"events_url": "https://api.github.com/repos/mralexgray/Alfred-Google-Translate/events",
	"assignees_url": "https://api.github.com/repos/mralexgray/Alfred-Google-Translate/assignees{/user}",
	"branches_url": "https://api.github.com/repos/mralexgray/Alfred-Google-Translate/branches{/branch}",
	"tags_url": "https://api.github.com/repos/mralexgray/Alfred-Google-Translate/tags",
	"blobs_url": "https://api.github.com/repos/mralexgray/Alfred-Google-Translate/git/blobs{/sha}",
	"git_tags_url": "https://api.github.com/repos/mralexgray/Alfred-Google-Translate/git/tags{/sha}",
	"git_refs_url": "https://api.github.com/repos/mralexgray/Alfred-Google-Translate/git/refs{/sha}",
	"trees_url": "https://api.github.com/repos/mralexgray/Alfred-Google-Translate/git/trees{/sha}",
	"statuses_url": "https://api.github.com/repos/mralexgray/Alfred-Google-Translate/statuses/{sha}",
	"languages_url": "https://api.github.com/repos/mralexgray/Alfred-Google-Translate/languages",
	"stargazers_url": "https://api.github.com/repos/mralexgray/Alfred-Google-Translate/stargazers",
	"contributors_url": "https://api.github.com/repos/mralexgray/Alfred-Google-Translate/contributors",
	"subscribers_url": "https://api.github.com/repos/mralexgray/Alfred-Google-Translate/subscribers",
	"subscription_url": "https://api.github.com/repos/mralexgray/Alfred-Google-Translate/subscription",
	"commits_url": "https://api.github.com/repos/mralexgray/Alfred-Google-Translate/commits{/sha}",
	"git_commits_url": "https://api.github.com/repos/mralexgray/Alfred-Google-Translate/git/commits{/sha}",
	"comments_url": "https://api.github.com/repos/mralexgray/Alfred-Google-Translate/comments{/number}",
	"issue_comment_url": "https://api.github.com/repos/mralexgray/Alfred-Google-Translate/issues/comments{/number}",
	"contents_url": "https://api.github.com/repos/mralexgray/Alfred-Google-Translate/contents/{+path}",
	"compare_url": "https://api.github.com/repos/mralexgray/Alfred-Google-Translate/compare/{base}...{head}",
	"merges_url": "https://api.github.com/repos/mralexgray/Alfred-Google-Translate/merges",
	"archive_url": "https://api.github.com/repos/mralexgray/Alfred-Google-Translate/{archive_format}{/ref}",
	"downloads_url": "https://api.github.com/repos/mralexgray/Alfred-Google-Translate/downloads",
	"issues_url": "https://api.github.com/repos/mralexgray/Alfred-Google-Translate/issues{/number}",
	"pulls_url": "https://api.github.com/repos/mralexgray/Alfred-Google-Translate/pulls{/number}",
	"milestones_url": "https://api.github.com/repos/mralexgray/Alfred-Google-Translate/milestones{/number}",
	"notifications_url": "https://api.github.com/repos/mralexgray/Alfred-Google-Translate/notifications{?since,all,participating}",
	"labels_url": "https://api.github.com/repos/mralexgray/Alfred-Google-Translate/labels{/name}",
	"releases_url": "https://api.github.com/repos/mralexgray/Alfred-Google-Translate/releases{/id}",
	"deployments_url": "https://api.github.com/repos/mralexgray/Alfred-Google-Translate/deployments",
	"created_at": "2013-06-04T10:45:10Z",
	"updated_at": "2013-06-04T10:45:10Z",
	"pushed_at": "2013-01-12T19:39:03Z",
	"git_url": "git://github.com/mralexgray/Alfred-Google-Translate.git",
	"ssh_url": "git@github.com:mralexgray/Alfred-Google-Translate.git",
	"clone_url": "https://github.com/mralexgray/Alfred-Google-Translate.git",
	"svn_url": "https://github.com/mralexgray/Alfred-Google-Translate",
	"homepage": null,
	"size": 103,
	"stargazers_count": 0,
	"watchers_count": 0,
	"language": "Shell",
	"has_issues": false,
	"has_downloads": true,
	"has_wiki": true,
	"has_pages": false,
	"forks_count": 0,
	"mirror_url": null,
	"open_issues_count": 0,
	"forks": 0,
	"open_issues": 0,
	"watchers": 0,
	"default_branch": "master"
},
"second": {
	"private": false,
	"html_url": "https://github.com/mralexgray/Alfred-Google-Translate",
	"description": "Extension for Alfred that will do a Google translate for you",
	"fork": true,
	"url": "https://api.github.com/repos/mralexgray/Alfred-Google-Translate",
	"forks_url": "https://api.github.com/repos/mralexgray/Alfred-Google-Translate/forks",
	"keys_url": "https://api.github.com/repos/mralexgray/Alfred-Google-Translate/keys{/key_id}",
	"collaborators_url": "https://api.github.com/repos/mralexgray/Alfred-Google-Translate/collaborators{/collaborator}",
	"teams_url": "https://api.github.com/repos/mralexgray/Alfred-Google-Translate/teams",
	"hooks_url": "https://api.github.com/repos/mralexgray/Alfred-Google-Translate/hooks",
	"issue_events_url": "https://api.github.com/repos/mralexgray/Alfred-Google-Translate/issues/events{/number}",
	"events_url": "https://api.github.com/repos/mralexgray/Alfred-Google-Translate/events",
	"assignees_url": "https://api.github.com/repos/mralexgray/Alfred-Google-Translate/assignees{/user}",
	"branches_url": "https://api.github.com/repos/mralexgray/Alfred-Google-Translate/branches{/branch}",
	"tags_url": "https://api.github.com/repos/mralexgray/Alfred-Google-Translate/tags",
	"blobs_url": "https://api.github.com/repos/mralexgray/Alfred-Google-Translate/git/blobs{/sha}",
	"git_tags_url": "https://api.github.com/repos/mralexgray/Alfred-Google-Translate/git/tags{/sha}",
	"git_refs_url": "https://api.github.com/repos/mralexgray/Alfred-Google-Translate/git/refs{/sha}",
	"trees_url": "https://api.github.com/repos/mralexgray/Alfred-Google-Translate/git/trees{/sha}",
	"statuses_url": "https://api.github.com/repos/mralexgray/Alfred-Google-Translate/statuses/{sha}",
	"languages_url": "https://api.github.com/repos/mralexgray/Alfred-Google-Translate/languages",
	"stargazers_url": "https://api.github.com/repos/mralexgray/Alfred-Google-Translate/stargazers",
	"contributors_url": "https://api.github.com/repos/mralexgray/Alfred-Google-Translate/contributors",
	"subscribers_url": "https://api.github.com/repos/mralexgray/Alfred-Google-Translate/subscribers",
	"subscription_url": "https://api.github.com/repos/mralexgray/Alfred-Google-Translate/subscription",
	"commits_url": "https://api.github.com/repos/mralexgray/Alfred-Google-Translate/commits{/sha}",
	"git_commits_url": "https://api.github.com/repos/mralexgray/Alfred-Google-Translate/git/commits{/sha}",
	"comments_url": "https://api.github.com/repos/mralexgray/Alfred-Google-Translate/comments{/number}",
	"issue_comment_url": "https://api.github.com/repos/mralexgray/Alfred-Google-Translate/issues/comments{/number}",
	"contents_url": "https://api.github.com/repos/mralexgray/Alfred-Google-Translate/contents/{+path}",
	"compare_url": "https://api.github.com/repos/mralexgray/Alfred-Google-Translate/compare/{base}...{head}",
	"merges_url": "https://api.github.com/repos/mralexgray/Alfred-Google-Translate/merges",
	"archive_url": "https://api.github.com/repos/mralexgray/Alfred-Google-Translate/{archive_format}{/ref}",
	"downloads_url": "https://api.github.com/repos/mralexgray/Alfred-Google-Translate/downloads",
	"issues_url": "https://api.github.com/repos/mralexgray/Alfred-Google-Translate/issues{/number}",
	"pulls_url": "https://api.github.com/repos/mralexgray/Alfred-Google-Translate/pulls{/number}",
	"milestones_url": "https://api.github.com/repos/mralexgray/Alfred-Google-Translate/milestones{/number}",
	"notifications_url": "https://api.github.com/repos/mralexgray/Alfred-Google-Translate/notifications{?since,all,participating}",
	"labels_url": "https://api.github.com/repos/mralexgray/Alfred-Google-Translate/labels{/name}",
	"releases_url": "https://api.github.com/repos/mralexgray/Alfred-Google-Translate/releases{/id}",
	"deployments_url": "https://api.github.com/repos/mralexgray/Alfred-Google-Translate/deployments",
	"created_at": "2013-06-04T10:45:10Z",
	"updated_at": "2013-06-04T10:45:10Z",
	"pushed_at": "2013-01-12T19:39:03Z",
	"git_url": "git://github.com/mralexgray/Alfred-Google-Translate.git",
	"ssh_url": "git@github.com:mralexgray/Alfred-Google-Translate.git",
	"clone_url": "https://github.com/mralexgray/Alfred-Google-Translate.git",
	"svn_url": "https://github.com/mralexgray/Alfred-Google-Translate",
	"homepage": null,
	"size": 103,
	"stargazers_count": 0,
	"watchers_count": 0,
	"language": "Shell",
	"has_issues": false,
	"has_downloads": true,
	"has_wiki": true,
	"has_pages": false,
	"forks_count": 0,
	"mirror_url": null,
	"open_issues_count": 0,
	"forks": 0,
	"open_issues": 0,
	"watchers": 0,
	"default_branch": "master"
   }
}';

    SELECT JSON_VALUE(l_json, '$.second.watchers' ERROR ON ERROR) INTO l_var FROM dual;
    DBMS_OUTPUT.PUT_LINE('var: '||l_var);
END;

var: 0

Statement 2

CREATE TABLE my_table (json_column CLOB);

INSERT INTO my_table (json_column) values ('  {
"first": {
	"private": false,
	"html_url": "https://github.com/mralexgray/Alfred-Google-Translate",
	"description": "Extension for Alfred that will do a Google translate for you",
	"fork": true,
	"url": "https://api.github.com/repos/mralexgray/Alfred-Google-Translate",
	"forks_url": "https://api.github.com/repos/mralexgray/Alfred-Google-Translate/forks",
	"keys_url": "https://api.github.com/repos/mralexgray/Alfred-Google-Translate/keys{/key_id}",
	"collaborators_url": "https://api.github.com/repos/mralexgray/Alfred-Google-Translate/collaborators{/collaborator}",
	"teams_url": "https://api.github.com/repos/mralexgray/Alfred-Google-Translate/teams",
	"hooks_url": "https://api.github.com/repos/mralexgray/Alfred-Google-Translate/hooks",
	"issue_events_url": "https://api.github.com/repos/mralexgray/Alfred-Google-Translate/issues/events{/number}",
	"events_url": "https://api.github.com/repos/mralexgray/Alfred-Google-Translate/events",
	"assignees_url": "https://api.github.com/repos/mralexgray/Alfred-Google-Translate/assignees{/user}",
	"branches_url": "https://api.github.com/repos/mralexgray/Alfred-Google-Translate/branches{/branch}",
	"tags_url": "https://api.github.com/repos/mralexgray/Alfred-Google-Translate/tags",
	"blobs_url": "https://api.github.com/repos/mralexgray/Alfred-Google-Translate/git/blobs{/sha}",
	"git_tags_url": "https://api.github.com/repos/mralexgray/Alfred-Google-Translate/git/tags{/sha}",
	"git_refs_url": "https://api.github.com/repos/mralexgray/Alfred-Google-Translate/git/refs{/sha}",
	"trees_url": "https://api.github.com/repos/mralexgray/Alfred-Google-Translate/git/trees{/sha}",
	"statuses_url": "https://api.github.com/repos/mralexgray/Alfred-Google-Translate/statuses/{sha}",
	"languages_url": "https://api.github.com/repos/mralexgray/Alfred-Google-Translate/languages",
	"stargazers_url": "https://api.github.com/repos/mralexgray/Alfred-Google-Translate/stargazers",
	"contributors_url": "https://api.github.com/repos/mralexgray/Alfred-Google-Translate/contributors",
	"subscribers_url": "https://api.github.com/repos/mralexgray/Alfred-Google-Translate/subscribers",
	"subscription_url": "https://api.github.com/repos/mralexgray/Alfred-Google-Translate/subscription",
	"commits_url": "https://api.github.com/repos/mralexgray/Alfred-Google-Translate/commits{/sha}",
	"git_commits_url": "https://api.github.com/repos/mralexgray/Alfred-Google-Translate/git/commits{/sha}",
	"comments_url": "https://api.github.com/repos/mralexgray/Alfred-Google-Translate/comments{/number}",
	"issue_comment_url": "https://api.github.com/repos/mralexgray/Alfred-Google-Translate/issues/comments{/number}",
	"contents_url": "https://api.github.com/repos/mralexgray/Alfred-Google-Translate/contents/{+path}",
	"compare_url": "https://api.github.com/repos/mralexgray/Alfred-Google-Translate/compare/{base}...{head}",
	"merges_url": "https://api.github.com/repos/mralexgray/Alfred-Google-Translate/merges",
	"archive_url": "https://api.github.com/repos/mralexgray/Alfred-Google-Translate/{archive_format}{/ref}",
	"downloads_url": "https://api.github.com/repos/mralexgray/Alfred-Google-Translate/downloads",
	"issues_url": "https://api.github.com/repos/mralexgray/Alfred-Google-Translate/issues{/number}",
	"pulls_url": "https://api.github.com/repos/mralexgray/Alfred-Google-Translate/pulls{/number}",
	"milestones_url": "https://api.github.com/repos/mralexgray/Alfred-Google-Translate/milestones{/number}",
	"notifications_url": "https://api.github.com/repos/mralexgray/Alfred-Google-Translate/notifications{?since,all,participating}",
	"labels_url": "https://api.github.com/repos/mralexgray/Alfred-Google-Translate/labels{/name}",
	"releases_url": "https://api.github.com/repos/mralexgray/Alfred-Google-Translate/releases{/id}",
	"deployments_url": "https://api.github.com/repos/mralexgray/Alfred-Google-Translate/deployments",
	"created_at": "2013-06-04T10:45:10Z",
	"updated_at": "2013-06-04T10:45:10Z",
	"pushed_at": "2013-01-12T19:39:03Z",
	"git_url": "git://github.com/mralexgray/Alfred-Google-Translate.git",
	"ssh_url": "git@github.com:mralexgray/Alfred-Google-Translate.git",
	"clone_url": "https://github.com/mralexgray/Alfred-Google-Translate.git",
	"svn_url": "https://github.com/mralexgray/Alfred-Google-Translate",
	"homepage": null,
	"size": 103,
	"stargazers_count": 0,
	"watchers_count": 0,
	"language": "Shell",
	"has_issues": false,
	"has_downloads": true,
	"has_wiki": true,
	"has_pages": false,
	"forks_count": 0,
	"mirror_url": null,
	"open_issues_count": 0,
	"forks": 0,
	"open_issues": 0,
	"watchers": 0,
	"default_branch": "master"
},
"second": {
	"private": false,
	"html_url": "https://github.com/mralexgray/Alfred-Google-Translate",
	"description": "Extension for Alfred that will do a Google translate for you",
	"fork": true,
	"url": "https://api.github.com/repos/mralexgray/Alfred-Google-Translate",
	"forks_url": "https://api.github.com/repos/mralexgray/Alfred-Google-Translate/forks",
	"keys_url": "https://api.github.com/repos/mralexgray/Alfred-Google-Translate/keys{/key_id}",
	"collaborators_url": "https://api.github.com/repos/mralexgray/Alfred-Google-Translate/collaborators{/collaborator}",
	"teams_url": "https://api.github.com/repos/mralexgray/Alfred-Google-Translate/teams",
	"hooks_url": "https://api.github.com/repos/mralexgray/Alfred-Google-Translate/hooks",
	"issue_events_url": "https://api.github.com/repos/mralexgray/Alfred-Google-Translate/issues/events{/number}",
	"events_url": "https://api.github.com/repos/mralexgray/Alfred-Google-Translate/events",
	"assignees_url": "https://api.github.com/repos/mralexgray/Alfred-Google-Translate/assignees{/user}",
	"branches_url": "https://api.github.com/repos/mralexgray/Alfred-Google-Translate/branches{/branch}",
	"tags_url": "https://api.github.com/repos/mralexgray/Alfred-Google-Translate/tags",
	"blobs_url": "https://api.github.com/repos/mralexgray/Alfred-Google-Translate/git/blobs{/sha}",
	"git_tags_url": "https://api.github.com/repos/mralexgray/Alfred-Google-Translate/git/tags{/sha}",
	"git_refs_url": "https://api.github.com/repos/mralexgray/Alfred-Google-Translate/git/refs{/sha}",
	"trees_url": "https://api.github.com/repos/mralexgray/Alfred-Google-Translate/git/trees{/sha}",
	"statuses_url": "https://api.github.com/repos/mralexgray/Alfred-Google-Translate/statuses/{sha}",
	"languages_url": "https://api.github.com/repos/mralexgray/Alfred-Google-Translate/languages",
	"stargazers_url": "https://api.github.com/repos/mralexgray/Alfred-Google-Translate/stargazers",
	"contributors_url": "https://api.github.com/repos/mralexgray/Alfred-Google-Translate/contributors",
	"subscribers_url": "https://api.github.com/repos/mralexgray/Alfred-Google-Translate/subscribers",
	"subscription_url": "https://api.github.com/repos/mralexgray/Alfred-Google-Translate/subscription",
	"commits_url": "https://api.github.com/repos/mralexgray/Alfred-Google-Translate/commits{/sha}",
	"git_commits_url": "https://api.github.com/repos/mralexgray/Alfred-Google-Translate/git/commits{/sha}",
	"comments_url": "https://api.github.com/repos/mralexgray/Alfred-Google-Translate/comments{/number}",
	"issue_comment_url": "https://api.github.com/repos/mralexgray/Alfred-Google-Translate/issues/comments{/number}",
	"contents_url": "https://api.github.com/repos/mralexgray/Alfred-Google-Translate/contents/{+path}",
	"compare_url": "https://api.github.com/repos/mralexgray/Alfred-Google-Translate/compare/{base}...{head}",
	"merges_url": "https://api.github.com/repos/mralexgray/Alfred-Google-Translate/merges",
	"archive_url": "https://api.github.com/repos/mralexgray/Alfred-Google-Translate/{archive_format}{/ref}",
	"downloads_url": "https://api.github.com/repos/mralexgray/Alfred-Google-Translate/downloads",
	"issues_url": "https://api.github.com/repos/mralexgray/Alfred-Google-Translate/issues{/number}",
	"pulls_url": "https://api.github.com/repos/mralexgray/Alfred-Google-Translate/pulls{/number}",
	"milestones_url": "https://api.github.com/repos/mralexgray/Alfred-Google-Translate/milestones{/number}",
	"notifications_url": "https://api.github.com/repos/mralexgray/Alfred-Google-Translate/notifications{?since,all,participating}",
	"labels_url": "https://api.github.com/repos/mralexgray/Alfred-Google-Translate/labels{/name}",
	"releases_url": "https://api.github.com/repos/mralexgray/Alfred-Google-Translate/releases{/id}",
	"deployments_url": "https://api.github.com/repos/mralexgray/Alfred-Google-Translate/deployments",
	"created_at": "2013-06-04T10:45:10Z",
	"updated_at": "2013-06-04T10:45:10Z",
	"pushed_at": "2013-01-12T19:39:03Z",
	"git_url": "git://github.com/mralexgray/Alfred-Google-Translate.git",
	"ssh_url": "git@github.com:mralexgray/Alfred-Google-Translate.git",
	"clone_url": "https://github.com/mralexgray/Alfred-Google-Translate.git",
	"svn_url": "https://github.com/mralexgray/Alfred-Google-Translate",
	"homepage": null,
	"size": 103,
	"stargazers_count": 0,
	"watchers_count": 0,
	"language": "Shell",
	"has_issues": false,
	"has_downloads": true,
	"has_wiki": true,
	"has_pages": false,
	"forks_count": 0,
	"mirror_url": null,
	"open_issues_count": 0,
	"forks": 0,
	"open_issues": 0,
	"watchers": 0,
	"default_branch": "master"
   }
}');

DECLARE
           l_json clob;
           l_var varchar2(90);
BEGIN
    SELECT json_column into l_json from my_table;

    SELECT JSON_VALUE(l_json, '$.second.watchers' ERROR ON ERROR) INTO l_var FROM dual;
    DBMS_OUTPUT.PUT_LINE('var: '||l_var);
END;
/

drop table my_table purge;

Additional Information

Database on OTN SQL and PL/SQL Discussion forums
Oracle Database
Download Oracle Database