postgres extract int array from text
I have the following postgresql statement:
SELECT 1 = ANY( jsonb_array_elements_text('[2, 1, 3]') );
Basically I have a string which contains an array of integers seperated by comma, like: [1, 2, 3] and sometimes this array could be empty too, like: . Now, I want to write a query (as part of a bigger query) where I would be able to find out if an element is matching any integers in the text. For example:
SELECT 1 = ANY( jsonb_array_elements_text('[2, 1, 3]') ); -- Should return true
SELECT 1 = ANY( jsonb_array_elements_text('') ); -- should return false
However, the above query fails with an error message:
ERROR: op ANY/ALL (array) requires array on right side
LINE 1: SELECT 1 = ANY( jsonb_array_elements_text('[2, 1, 3]') );
Any help how I can extract an integer array out of a text so that I can use it in a join condition ?
I am using postgres 9.4 if it matters.
sql arrays json postgresql
asked Nov 14 '18 at 9:06
SankarSankar
2,27783458
add a comment |
I have the following postgresql statement:
SELECT 1 = ANY( jsonb_array_elements_text('[2, 1, 3]') );
Basically I have a string which contains an array of integers seperated by comma, like: [1, 2, 3] and sometimes this array could be empty too, like: . Now, I want to write a query (as part of a bigger query) where I would be able to find out if an element is matching any integers in the text. For example:
SELECT 1 = ANY( jsonb_array_elements_text('[2, 1, 3]') ); -- Should return true
SELECT 1 = ANY( jsonb_array_elements_text('') ); -- should return false
However, the above query fails with an error message:
ERROR: op ANY/ALL (array) requires array on right side
LINE 1: SELECT 1 = ANY( jsonb_array_elements_text('[2, 1, 3]') );
Any help how I can extract an integer array out of a text so that I can use it in a join condition ?
I am using postgres 9.4 if it matters.
sql arrays json postgresql
asked Nov 14 '18 at 9:06
SankarSankar
2,27783458
This would be a lot easier with a native integer array rather than JSON:1 = any(array[1,2,3])
– a_horse_with_no_name
Nov 14 '18 at 9:33
Yes, no doubt. But this is part of a bigger query and so I have simplified here for brevity.
– Sankar
Nov 14 '18 at 9:35
add a comment |
I have the following postgresql statement:
SELECT 1 = ANY( jsonb_array_elements_text('[2, 1, 3]') );
Basically I have a string which contains an array of integers seperated by comma, like: [1, 2, 3] and sometimes this array could be empty too, like: . Now, I want to write a query (as part of a bigger query) where I would be able to find out if an element is matching any integers in the text. For example:
SELECT 1 = ANY( jsonb_array_elements_text('[2, 1, 3]') ); -- Should return true
SELECT 1 = ANY( jsonb_array_elements_text('') ); -- should return false
However, the above query fails with an error message:
ERROR: op ANY/ALL (array) requires array on right side
LINE 1: SELECT 1 = ANY( jsonb_array_elements_text('[2, 1, 3]') );
Any help how I can extract an integer array out of a text so that I can use it in a join condition ?
I am using postgres 9.4 if it matters.
sql arrays json postgresql
asked Nov 14 '18 at 9:06
SankarSankar
2,27783458
I have the following postgresql statement:
SELECT 1 = ANY( jsonb_array_elements_text('[2, 1, 3]') );
Basically I have a string which contains an array of integers seperated by comma, like: [1, 2, 3] and sometimes this array could be empty too, like: . Now, I want to write a query (as part of a bigger query) where I would be able to find out if an element is matching any integers in the text. For example:
SELECT 1 = ANY( jsonb_array_elements_text('[2, 1, 3]') ); -- Should return true
SELECT 1 = ANY( jsonb_array_elements_text('') ); -- should return false
However, the above query fails with an error message:
ERROR: op ANY/ALL (array) requires array on right side
LINE 1: SELECT 1 = ANY( jsonb_array_elements_text('[2, 1, 3]') );
Any help how I can extract an integer array out of a text so that I can use it in a join condition ?
I am using postgres 9.4 if it matters.
sql arrays json postgresql
sql arrays json postgresql
asked Nov 14 '18 at 9:06
SankarSankar
2,27783458
asked Nov 14 '18 at 9:06
SankarSankar
2,27783458
asked Nov 14 '18 at 9:06
SankarSankar
2,27783458
asked Nov 14 '18 at 9:06
SankarSankar
2,27783458
asked Nov 14 '18 at 9:06
SankarSankar
2,27783458
2,27783458
This would be a lot easier with a native integer array rather than JSON:1 = any(array[1,2,3])
– a_horse_with_no_name
Nov 14 '18 at 9:33
Yes, no doubt. But this is part of a bigger query and so I have simplified here for brevity.
– Sankar
Nov 14 '18 at 9:35
add a comment |
This would be a lot easier with a native integer array rather than JSON:1 = any(array[1,2,3])
– a_horse_with_no_name
Nov 14 '18 at 9:33
Yes, no doubt. But this is part of a bigger query and so I have simplified here for brevity.
– Sankar
Nov 14 '18 at 9:35
This would be a lot easier with a native integer array rather than JSON:
1 = any(array[1,2,3])– a_horse_with_no_name
Nov 14 '18 at 9:33
This would be a lot easier with a native integer array rather than JSON:
1 = any(array[1,2,3])– a_horse_with_no_name
Nov 14 '18 at 9:33
Yes, no doubt. But this is part of a bigger query and so I have simplified here for brevity.
– Sankar
Nov 14 '18 at 9:35
Yes, no doubt. But this is part of a bigger query and so I have simplified here for brevity.
– Sankar
Nov 14 '18 at 9:35
add a comment |
1 Answer
1
active
oldest
votes
I have found it. The answer is:
SELECT 1 IN (SELECT json_array_elements('[2, 1, 3]')::text::int);
SELECT 1 IN (SELECT json_array_elements('')::text::int);
SELECT 1 IN (SELECT json_array_elements('[12, 10, 3]')::text::int);
answered Nov 14 '18 at 10:00
SankarSankar
2,27783458
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%2f53296445%2fpostgres-extract-int-array-from-text%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 have found it. The answer is:
SELECT 1 IN (SELECT json_array_elements('[2, 1, 3]')::text::int);
SELECT 1 IN (SELECT json_array_elements('')::text::int);
SELECT 1 IN (SELECT json_array_elements('[12, 10, 3]')::text::int);
answered Nov 14 '18 at 10:00
SankarSankar
2,27783458
add a comment |
I have found it. The answer is:
SELECT 1 IN (SELECT json_array_elements('[2, 1, 3]')::text::int);
SELECT 1 IN (SELECT json_array_elements('')::text::int);
SELECT 1 IN (SELECT json_array_elements('[12, 10, 3]')::text::int);
answered Nov 14 '18 at 10:00
SankarSankar
2,27783458
add a comment |
I have found it. The answer is:
SELECT 1 IN (SELECT json_array_elements('[2, 1, 3]')::text::int);
SELECT 1 IN (SELECT json_array_elements('')::text::int);
SELECT 1 IN (SELECT json_array_elements('[12, 10, 3]')::text::int);
answered Nov 14 '18 at 10:00
SankarSankar
2,27783458
I have found it. The answer is:
SELECT 1 IN (SELECT json_array_elements('[2, 1, 3]')::text::int);
SELECT 1 IN (SELECT json_array_elements('')::text::int);
SELECT 1 IN (SELECT json_array_elements('[12, 10, 3]')::text::int);
answered Nov 14 '18 at 10:00
SankarSankar
2,27783458
answered Nov 14 '18 at 10:00
SankarSankar
2,27783458
answered Nov 14 '18 at 10:00
SankarSankar
2,27783458
answered Nov 14 '18 at 10:00
SankarSankar
2,27783458
2,27783458
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%2f53296445%2fpostgres-extract-int-array-from-text%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
This would be a lot easier with a native integer array rather than JSON:
1 = any(array[1,2,3])– a_horse_with_no_name
Nov 14 '18 at 9:33
Yes, no doubt. But this is part of a bigger query and so I have simplified here for brevity.
– Sankar
Nov 14 '18 at 9:35