Check if one json value is in the json value from another JSON, for each key
With SQL in postgres, I want to know if one JSON is 'IN' another JSON.
For example:
json_1 = {"a": ["123"], "b": ["456", "789"]}
json_2 = {"a": ["123"], "b": ["456"]}
In the above case, json_2["a"] is in json_1["a"] and json_2["b"] is in json_1["b"].
If I would know all the possible keys of the json, I would easily be able to write the above per key. However, the problem is that I don't know how many and which keys are in the JSON. How can I check for every key in the JSON, if json_2 is in json_1?
sql json postgresql compare
asked Nov 27 '18 at 14:32
MarjoleinMarjolein
436
add a comment |
With SQL in postgres, I want to know if one JSON is 'IN' another JSON.
For example:
json_1 = {"a": ["123"], "b": ["456", "789"]}
json_2 = {"a": ["123"], "b": ["456"]}
In the above case, json_2["a"] is in json_1["a"] and json_2["b"] is in json_1["b"].
If I would know all the possible keys of the json, I would easily be able to write the above per key. However, the problem is that I don't know how many and which keys are in the JSON. How can I check for every key in the JSON, if json_2 is in json_1?
sql json postgresql compare
asked Nov 27 '18 at 14:32
MarjoleinMarjolein
436
So json_1/2 can contain any number of keys with any names, and each of those keys will always be an array, and for each array in json_2, you want to check the corresponding key (which may not exist) in json_1, and then see if all the values from the json_2 array are found in the json_1 array in any order?
– 404
Nov 27 '18 at 14:45
Correct. However, if the key is in json_2, I do know it also will be in json_1. So the 'may not exist' part doesn't hold. However, the value of the key in json_1 could be then.
– Marjolein
Nov 27 '18 at 15:16
add a comment |
With SQL in postgres, I want to know if one JSON is 'IN' another JSON.
For example:
json_1 = {"a": ["123"], "b": ["456", "789"]}
json_2 = {"a": ["123"], "b": ["456"]}
In the above case, json_2["a"] is in json_1["a"] and json_2["b"] is in json_1["b"].
If I would know all the possible keys of the json, I would easily be able to write the above per key. However, the problem is that I don't know how many and which keys are in the JSON. How can I check for every key in the JSON, if json_2 is in json_1?
sql json postgresql compare
asked Nov 27 '18 at 14:32
MarjoleinMarjolein
436
With SQL in postgres, I want to know if one JSON is 'IN' another JSON.
For example:
json_1 = {"a": ["123"], "b": ["456", "789"]}
json_2 = {"a": ["123"], "b": ["456"]}
In the above case, json_2["a"] is in json_1["a"] and json_2["b"] is in json_1["b"].
If I would know all the possible keys of the json, I would easily be able to write the above per key. However, the problem is that I don't know how many and which keys are in the JSON. How can I check for every key in the JSON, if json_2 is in json_1?
sql json postgresql compare
sql json postgresql compare
asked Nov 27 '18 at 14:32
MarjoleinMarjolein
436
asked Nov 27 '18 at 14:32
MarjoleinMarjolein
436
asked Nov 27 '18 at 14:32
MarjoleinMarjolein
436
asked Nov 27 '18 at 14:32
MarjoleinMarjolein
436
asked Nov 27 '18 at 14:32
MarjoleinMarjolein
436
436
So json_1/2 can contain any number of keys with any names, and each of those keys will always be an array, and for each array in json_2, you want to check the corresponding key (which may not exist) in json_1, and then see if all the values from the json_2 array are found in the json_1 array in any order?
– 404
Nov 27 '18 at 14:45
Correct. However, if the key is in json_2, I do know it also will be in json_1. So the 'may not exist' part doesn't hold. However, the value of the key in json_1 could be then.
– Marjolein
Nov 27 '18 at 15:16
add a comment |
So json_1/2 can contain any number of keys with any names, and each of those keys will always be an array, and for each array in json_2, you want to check the corresponding key (which may not exist) in json_1, and then see if all the values from the json_2 array are found in the json_1 array in any order?
– 404
Nov 27 '18 at 14:45
Correct. However, if the key is in json_2, I do know it also will be in json_1. So the 'may not exist' part doesn't hold. However, the value of the key in json_1 could be then.
– Marjolein
Nov 27 '18 at 15:16
So json_1/2 can contain any number of keys with any names, and each of those keys will always be an array, and for each array in json_2, you want to check the corresponding key (which may not exist) in json_1, and then see if all the values from the json_2 array are found in the json_1 array in any order?
– 404
Nov 27 '18 at 14:45
So json_1/2 can contain any number of keys with any names, and each of those keys will always be an array, and for each array in json_2, you want to check the corresponding key (which may not exist) in json_1, and then see if all the values from the json_2 array are found in the json_1 array in any order?
– 404
Nov 27 '18 at 14:45
Correct. However, if the key is in json_2, I do know it also will be in json_1. So the 'may not exist' part doesn't hold. However, the value of the key in json_1 could be then.
– Marjolein
Nov 27 '18 at 15:16
Correct. However, if the key is in json_2, I do know it also will be in json_1. So the 'may not exist' part doesn't hold. However, the value of the key in json_1 could be then.
– Marjolein
Nov 27 '18 at 15:16
add a comment |
1 Answer
1
active
oldest
votes
I'm not sure about what output format you want, but this will create one row per key, with a boolean stating whether the json_2 key's array values are contained within the json_1 key's values.
CREATE TABLE t (json_1 JSONB, json_2 JSONB);
INSERT INTO t
VALUES
('{"a":["123"],"b":["456","789","aaa"],"c":["999"],"d":}',
'{"a":["123"],"b":["789","456"],"c":["123"],"d":["x"]}');
Query #1
SELECT key, value <@ (json_1->key) AS contained
FROM (
SELECT (JSONB_EACH(json_2)).*, json_1
FROM t
) j;
Returns:
| key | contained |
| --- | --------- |
| a | true |
| b | true |
| c | false |
| d | false |
View on DB Fiddle
answered Nov 27 '18 at 15:51
404404
3,1401726
add a comment |
Your Answer
StackExchange.ifUsing("editor", function () {
StackExchange.using("externalEditor", function () {
StackExchange.using("snippets", function () {
StackExchange.snippets.init();
});
});
}, "code-snippets");
StackExchange.ready(function() {
var channelOptions = {
tags: "".split(" "),
id: "1"
};
initTagRenderer("".split(" "), "".split(" "), channelOptions);
StackExchange.using("externalEditor", function() {
// Have to fire editor after snippets, if snippets enabled
if (StackExchange.settings.snippets.snippetsEnabled) {
StackExchange.using("snippets", function() {
createEditor();
});
}
else {
createEditor();
}
});
function createEditor() {
StackExchange.prepareEditor({
heartbeatType: 'answer',
autoActivateHeartbeat: false,
convertImagesToLinks: true,
noModals: true,
showLowRepImageUploadWarning: true,
reputationToPostImages: 10,
bindNavPrevention: true,
postfix: "",
imageUploader: {
brandingHtml: "Powered by u003ca class="icon-imgur-white" href="https://imgur.com/"u003eu003c/au003e",
contentPolicyHtml: "User contributions licensed under u003ca href="https://creativecommons.org/licenses/by-sa/3.0/"u003ecc by-sa 3.0 with attribution requiredu003c/au003e u003ca href="https://stackoverflow.com/legal/content-policy"u003e(content policy)u003c/au003e",
allowUrls: true
},
onDemand: true,
discardSelector: ".discard-answer"
,immediatelyShowMarkdownHelp:true
});
}
});
Sign up or log in
StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
StackExchange.ready(
function () {
StackExchange.openid.initPostLogin('.new-post-login', 'https%3a%2f%2fstackoverflow.com%2fquestions%2f53501992%2fcheck-if-one-json-value-is-in-the-json-value-from-another-json-for-each-key%23new-answer', 'question_page');
}
);
Post as a guest
Required, but never shown
1 Answer
1
active
oldest
votes
1 Answer
1
active
oldest
votes
active
oldest
votes
active
oldest
votes
I'm not sure about what output format you want, but this will create one row per key, with a boolean stating whether the json_2 key's array values are contained within the json_1 key's values.
CREATE TABLE t (json_1 JSONB, json_2 JSONB);
INSERT INTO t
VALUES
('{"a":["123"],"b":["456","789","aaa"],"c":["999"],"d":}',
'{"a":["123"],"b":["789","456"],"c":["123"],"d":["x"]}');
Query #1
SELECT key, value <@ (json_1->key) AS contained
FROM (
SELECT (JSONB_EACH(json_2)).*, json_1
FROM t
) j;
Returns:
| key | contained |
| --- | --------- |
| a | true |
| b | true |
| c | false |
| d | false |
View on DB Fiddle
answered Nov 27 '18 at 15:51
404404
3,1401726
add a comment |
I'm not sure about what output format you want, but this will create one row per key, with a boolean stating whether the json_2 key's array values are contained within the json_1 key's values.
CREATE TABLE t (json_1 JSONB, json_2 JSONB);
INSERT INTO t
VALUES
('{"a":["123"],"b":["456","789","aaa"],"c":["999"],"d":}',
'{"a":["123"],"b":["789","456"],"c":["123"],"d":["x"]}');
Query #1
SELECT key, value <@ (json_1->key) AS contained
FROM (
SELECT (JSONB_EACH(json_2)).*, json_1
FROM t
) j;
Returns:
| key | contained |
| --- | --------- |
| a | true |
| b | true |
| c | false |
| d | false |
View on DB Fiddle
answered Nov 27 '18 at 15:51
404404
3,1401726
add a comment |
I'm not sure about what output format you want, but this will create one row per key, with a boolean stating whether the json_2 key's array values are contained within the json_1 key's values.
CREATE TABLE t (json_1 JSONB, json_2 JSONB);
INSERT INTO t
VALUES
('{"a":["123"],"b":["456","789","aaa"],"c":["999"],"d":}',
'{"a":["123"],"b":["789","456"],"c":["123"],"d":["x"]}');
Query #1
SELECT key, value <@ (json_1->key) AS contained
FROM (
SELECT (JSONB_EACH(json_2)).*, json_1
FROM t
) j;
Returns:
| key | contained |
| --- | --------- |
| a | true |
| b | true |
| c | false |
| d | false |
View on DB Fiddle
answered Nov 27 '18 at 15:51
404404
3,1401726
I'm not sure about what output format you want, but this will create one row per key, with a boolean stating whether the json_2 key's array values are contained within the json_1 key's values.
CREATE TABLE t (json_1 JSONB, json_2 JSONB);
INSERT INTO t
VALUES
('{"a":["123"],"b":["456","789","aaa"],"c":["999"],"d":}',
'{"a":["123"],"b":["789","456"],"c":["123"],"d":["x"]}');
Query #1
SELECT key, value <@ (json_1->key) AS contained
FROM (
SELECT (JSONB_EACH(json_2)).*, json_1
FROM t
) j;
Returns:
| key | contained |
| --- | --------- |
| a | true |
| b | true |
| c | false |
| d | false |
View on DB Fiddle
answered Nov 27 '18 at 15:51
404404
3,1401726
answered Nov 27 '18 at 15:51
404404
3,1401726
answered Nov 27 '18 at 15:51
404404
3,1401726
answered Nov 27 '18 at 15:51
404404
3,1401726
3,1401726
add a comment |
add a comment |
Thanks for contributing an answer to Stack Overflow!
- Please be sure to answer the question. Provide details and share your research!
But avoid …
- Asking for help, clarification, or responding to other answers.
- Making statements based on opinion; back them up with references or personal experience.
To learn more, see our tips on writing great answers.
Sign up or log in
StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
StackExchange.ready(
function () {
StackExchange.openid.initPostLogin('.new-post-login', 'https%3a%2f%2fstackoverflow.com%2fquestions%2f53501992%2fcheck-if-one-json-value-is-in-the-json-value-from-another-json-for-each-key%23new-answer', 'question_page');
}
);
Post as a guest
Required, but never shown
Sign up or log in
StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
Sign up or log in
StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
Sign up or log in
StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
So json_1/2 can contain any number of keys with any names, and each of those keys will always be an array, and for each array in json_2, you want to check the corresponding key (which may not exist) in json_1, and then see if all the values from the json_2 array are found in the json_1 array in any order?
– 404
Nov 27 '18 at 14:45
Correct. However, if the key is in json_2, I do know it also will be in json_1. So the 'may not exist' part doesn't hold. However, the value of the key in json_1 could be then.
– Marjolein
Nov 27 '18 at 15:16