0

In a postgresql database, I have a table with 2 columns:

Column1 is of type VARCHAR and contains multiple rows of ids in this format: ID1123312

Column2 is of type JSONB and contains multiple rows of jsons which have this structure:

{
    "a": [
        {
            "a1": "2022-01-01",
            "a2": "2026-01-31",
            "a3": 1
        }
    ],
    "b": [
        {
            "b1": "J",
            "b2": "1231342138adc2fehj3j21321321321kjk1423j32k9"
        }
    ],
    "c": [
        {
            "c1-1": "2021-02-01",
            "c1-2": "2021-01-01"
        },
        {
            "c2-1": "2021-04-01",
            "c2-2": "2021-03-01"
        }
    ]
}

I need to build a SELECT statement to return only the values of the b2 property from all rows of Column2. If it has just one row, then just '1231342138adc2fehj3j21321321321kjk1423j32k9'. If it has multiple rows, any value that it finds inside each jsonb inside each row.

Any help is greatly appreciated.

I tried:

SELECT *
FROM t
WHERE jsonb_path_exists(t.jsonColumn, '$.b[*].b2 ? (@ == "1231342138adc2fehj3j21321321321kjk1423j32k9")')

SELECT * returns the whole row of the table but I don't know how to instruct it to return just whatever value the b2 property contains (nested inside the json from the row) .

6
  • 2
    SELECT jsonb_path_query_first(t.jsonColumn, '$.b[*].b2 ? (@ == "1231342138adc2fehj3j21321321321kjk1423j32k9")') FROM t maybe? Did you even look at the link I posted on your previous question postgresql.org/docs/12/… Sep 9 at 8:57
  • Thanks a lot! Yes, I did look at the link but I just didn't see this function - I kept scrolling down from that point in the document where the link opened and I did not see this, because it was above : jsonb_path_query_first - This is the info I lacked. Please post an answer and I will accept it. Additionally could you tell me what kind of wild card I could use instead of the hardcoded value, so that it returns any value at that specific path? I tried jsonb_path_query_first(t.jsonColumn, '$.b[*].b2 ? (@ == *)') FROM t ; but that does not work. It seems * is not the wildcard I need. Sep 9 at 9:15
  • 1
    Just remove the predicate? SELECT jsonb_path_query_first(t.jsonColumn, '$.b[*].b2') FROM t Sep 9 at 9:19
  • Yes, that does it, but it still leaves the leading and trailing quotes in the results: Like: "1231342138adc2fehj3j21321321321kjk1423j32k9" ; Do you know any trick to remove those? Sep 9 at 9:25
  • How many objects are there in the b array, and how do you want to show if there are multiple? Sep 9 at 9:31

1 Answer 1

1

It seems you only have a single object in each b array. Therefore you odn't actually need a path query, you can use normal JSON accessors.

-> retrieves a value as json(b) and ->> retrieves it as text

SELECT t.jsonColumn->'b'->0->>'b2'
FROM t
WHERE t.jsonColumn->'b'->0->>'b2' IN ('Value1', 'Value2');

db<>fiddle

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.