How can I generate a new row and column output from a nested key in a JSONB field?2019 Community Moderator ElectionHow to concatenate text from multiple rows into a single text string in SQL server?Can I concatenate multiple MySQL rows into one field?How can I SELECT rows with MAX(Column value), DISTINCT by another column in SQL?How can I get column names from a table in SQL Server?Selectively pluck keys and values based on array from JSONB columnHow to load data as nested JSONB from non-JSONB postgres tablesSum a field in a jsonb column with an Ecto queryHow to update a jsonb column and put text arrayPostgreSQL Jsonb column contain singe quote that doesn't allow query fieldsAdd new fields to nested JSON array in JSONB
Is a lawful good "antagonist" effective?
Make a transparent 448*448 image
Latest web browser compatible with Windows 98
Have researchers managed to "reverse time"? If so, what does that mean for physics?
How do I interpret this "sky cover" chart?
The use of "touch" and "touch on" in context
Professor being mistaken for a grad student
How is the Swiss post e-voting system supposed to work, and how was it wrong?
Rejected in 4th interview round citing insufficient years of experience
How could a scammer know the apps on my phone / iTunes account?
Should we release the security issues we found in our product as CVE or we can just update those on weekly release notes?
Is having access to past exams cheating and, if yes, could it be proven just by a good grade?
My adviser wants to be the first author
Who is our nearest planetary neighbor, on average?
Why must traveling waves have the same amplitude to form a standing wave?
How can I change step-down my variable input voltage? [Microcontroller]
Life insurance that covers only simultaneous/dual deaths
Know when to turn notes upside-down(eighth notes, sixteen notes, etc.)
It's a yearly task, alright
What is IP squat space
How to deal with a cynical class?
Why did it take so long to abandon sail after steamships were demonstrated?
Converting Functions to Arrow functions
What options are left, if Britain cannot decide?
How can I generate a new row and column output from a nested key in a JSONB field?
2019 Community Moderator ElectionHow to concatenate text from multiple rows into a single text string in SQL server?Can I concatenate multiple MySQL rows into one field?How can I SELECT rows with MAX(Column value), DISTINCT by another column in SQL?How can I get column names from a table in SQL Server?Selectively pluck keys and values based on array from JSONB columnHow to load data as nested JSONB from non-JSONB postgres tablesSum a field in a jsonb column with an Ecto queryHow to update a jsonb column and put text arrayPostgreSQL Jsonb column contain singe quote that doesn't allow query fieldsAdd new fields to nested JSON array in JSONB
Field JSONB Structure:
...
"field_name": "field_value",
"columns": [
...
"nested_key_1": "nested_value_1"
"nested_key_2": "nested_value_2"
,
"nested_key_1": "nested_value_1"
"nested_key_2": "nested_value_2"
,
],
Desired Output:
----------------------------------------------
| | nested_key_1 | "nested_key_2" |
----------------------------------------------
| 1 | nested_value_1 | nested_value_2 |
----------------------------------------------
| 2 | nested_value_1 | nested_value_2 |
----------------------------------------------
One of my attempts to achieve this:
select * from (
select cast (
json_array_elements(json_field::json->'columns') as text
) as column_data from table_name
) as temp
However, this just adds the content of the columns array to a single field.
How can I achieve my desired output?
sql postgresql
asked Mar 6 at 19:05
Rob FyffeRob Fyffe
3471415
add a comment |
Field JSONB Structure:
...
"field_name": "field_value",
"columns": [
...
"nested_key_1": "nested_value_1"
"nested_key_2": "nested_value_2"
,
"nested_key_1": "nested_value_1"
"nested_key_2": "nested_value_2"
,
],
Desired Output:
----------------------------------------------
| | nested_key_1 | "nested_key_2" |
----------------------------------------------
| 1 | nested_value_1 | nested_value_2 |
----------------------------------------------
| 2 | nested_value_1 | nested_value_2 |
----------------------------------------------
One of my attempts to achieve this:
select * from (
select cast (
json_array_elements(json_field::json->'columns') as text
) as column_data from table_name
) as temp
However, this just adds the content of the columns array to a single field.
How can I achieve my desired output?
sql postgresql
asked Mar 6 at 19:05
Rob FyffeRob Fyffe
3471415
add a comment |
Field JSONB Structure:
...
"field_name": "field_value",
"columns": [
...
"nested_key_1": "nested_value_1"
"nested_key_2": "nested_value_2"
,
"nested_key_1": "nested_value_1"
"nested_key_2": "nested_value_2"
,
],
Desired Output:
----------------------------------------------
| | nested_key_1 | "nested_key_2" |
----------------------------------------------
| 1 | nested_value_1 | nested_value_2 |
----------------------------------------------
| 2 | nested_value_1 | nested_value_2 |
----------------------------------------------
One of my attempts to achieve this:
select * from (
select cast (
json_array_elements(json_field::json->'columns') as text
) as column_data from table_name
) as temp
However, this just adds the content of the columns array to a single field.
How can I achieve my desired output?
sql postgresql
asked Mar 6 at 19:05
Rob FyffeRob Fyffe
3471415
Field JSONB Structure:
...
"field_name": "field_value",
"columns": [
...
"nested_key_1": "nested_value_1"
"nested_key_2": "nested_value_2"
,
"nested_key_1": "nested_value_1"
"nested_key_2": "nested_value_2"
,
],
Desired Output:
----------------------------------------------
| | nested_key_1 | "nested_key_2" |
----------------------------------------------
| 1 | nested_value_1 | nested_value_2 |
----------------------------------------------
| 2 | nested_value_1 | nested_value_2 |
----------------------------------------------
One of my attempts to achieve this:
select * from (
select cast (
json_array_elements(json_field::json->'columns') as text
) as column_data from table_name
) as temp
However, this just adds the content of the columns array to a single field.
How can I achieve my desired output?
sql postgresql
sql postgresql
asked Mar 6 at 19:05
Rob FyffeRob Fyffe
3471415
asked Mar 6 at 19:05
Rob FyffeRob Fyffe
3471415
asked Mar 6 at 19:05
Rob FyffeRob Fyffe
3471415
asked Mar 6 at 19:05
Rob FyffeRob Fyffe
3471415
asked Mar 6 at 19:05
Rob FyffeRob Fyffe
3471415
3471415
add a comment |
add a comment |
1 Answer
1
active
oldest
votes
use json_to_recordset
SELECT x.*
FROM pjson_table
, json_to_recordset(myjson::json) x
( nested_key_1 text
, nested_key_2 text
)
answered Mar 6 at 19:08
Zaynul Abadin TuhinZaynul Abadin Tuhin
16.7k21033
1
Perfect, exactly what I was looking for.
– Rob Fyffe
Mar 7 at 17:43
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%2f55030459%2fhow-can-i-generate-a-new-row-and-column-output-from-a-nested-key-in-a-jsonb-fiel%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
use json_to_recordset
SELECT x.*
FROM pjson_table
, json_to_recordset(myjson::json) x
( nested_key_1 text
, nested_key_2 text
)
answered Mar 6 at 19:08
Zaynul Abadin TuhinZaynul Abadin Tuhin
16.7k21033
1
Perfect, exactly what I was looking for.
– Rob Fyffe
Mar 7 at 17:43
add a comment |
use json_to_recordset
SELECT x.*
FROM pjson_table
, json_to_recordset(myjson::json) x
( nested_key_1 text
, nested_key_2 text
)
answered Mar 6 at 19:08
Zaynul Abadin TuhinZaynul Abadin Tuhin
16.7k21033
1
Perfect, exactly what I was looking for.
– Rob Fyffe
Mar 7 at 17:43
add a comment |
use json_to_recordset
SELECT x.*
FROM pjson_table
, json_to_recordset(myjson::json) x
( nested_key_1 text
, nested_key_2 text
)
answered Mar 6 at 19:08
Zaynul Abadin TuhinZaynul Abadin Tuhin
16.7k21033
use json_to_recordset
SELECT x.*
FROM pjson_table
, json_to_recordset(myjson::json) x
( nested_key_1 text
, nested_key_2 text
)
answered Mar 6 at 19:08
Zaynul Abadin TuhinZaynul Abadin Tuhin
16.7k21033
answered Mar 6 at 19:08
Zaynul Abadin TuhinZaynul Abadin Tuhin
16.7k21033
answered Mar 6 at 19:08
Zaynul Abadin TuhinZaynul Abadin Tuhin
16.7k21033
answered Mar 6 at 19:08
Zaynul Abadin TuhinZaynul Abadin Tuhin
16.7k21033
16.7k21033
1
Perfect, exactly what I was looking for.
– Rob Fyffe
Mar 7 at 17:43
add a comment |
1
Perfect, exactly what I was looking for.
– Rob Fyffe
Mar 7 at 17:43
1
1
Perfect, exactly what I was looking for.
– Rob Fyffe
Mar 7 at 17:43
Perfect, exactly what I was looking for.
– Rob Fyffe
Mar 7 at 17:43
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%2f55030459%2fhow-can-i-generate-a-new-row-and-column-output-from-a-nested-key-in-a-jsonb-fiel%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
