Thread: extract property value from set of json arrays
I have the following in a postgresql table
row 1: {"a": 1, "b": "[{"c": "123", "d":"456", "e": "789"}, {"c": "222", "d":"111", "e": "000"} ]"}
row 2: {"a": 2, "b": "[{"c": "XXX", "d":"YYY", "e": "ZZZ"}, {"c": "666", "d":"444", "e": "333"} ]"}
How do I pullout all "b":"e" values and end up with this result:
789
000
ZZZ
333
On Mon, Apr 6, 2020 at 7:21 PM AC Gomez <antklc@gmail.com> wrote:
I have the following in a postgresql tablerow 1: {"a": 1, "b": "[{"c": "123", "d":"456", "e": "789"}, {"c": "222", "d":"111", "e": "000"} ]"}row 2: {"a": 2, "b": "[{"c": "XXX", "d":"YYY", "e": "ZZZ"}, {"c": "666", "d":"444", "e": "333"} ]"}How do I pullout all "b":"e" values and end up with this result:789000ZZZ333
Two approaches:
1. Wait for someone else to figure it out and give you the answer.
If 1. takes too long:
2. Read up on json operators.
Then work out something that either works or gets you at least close. If you are just close post the work done to date and any relevant points of confusion.
I'll then likely be willing and able to fill in the missing gap(s) and provide a relevant explanation.
You should formulate your query so that it doesn't require CREATE TABLE. WITH vals (v) AS (VALUES (''::json)) SELECT vals.v FROM vals; makes experimenting very easy.
Also, indicate which version of PostgreSQL you are working with.
David J.
figured it out:
from tbl_t t
cross join lateral jsonb_array_elements((t.jdata->>'b')::jsonb)
as c(e)
On Mon, Apr 6, 2020 at 10:51 PM David G. Johnston <david.g.johnston@gmail.com> wrote:
On Mon, Apr 6, 2020 at 7:21 PM AC Gomez <antklc@gmail.com> wrote:I have the following in a postgresql tablerow 1: {"a": 1, "b": "[{"c": "123", "d":"456", "e": "789"}, {"c": "222", "d":"111", "e": "000"} ]"}row 2: {"a": 2, "b": "[{"c": "XXX", "d":"YYY", "e": "ZZZ"}, {"c": "666", "d":"444", "e": "333"} ]"}How do I pullout all "b":"e" values and end up with this result:789000ZZZ333Two approaches:1. Wait for someone else to figure it out and give you the answer.If 1. takes too long:2. Read up on json operators.Then work out something that either works or gets you at least close. If you are just close post the work done to date and any relevant points of confusion.I'll then likely be willing and able to fill in the missing gap(s) and provide a relevant explanation.You should formulate your query so that it doesn't require CREATE TABLE. WITH vals (v) AS (VALUES (''::json)) SELECT vals.v FROM vals; makes experimenting very easy.Also, indicate which version of PostgreSQL you are working with.David J.