How do I query inside a JSONB column when all values have the same json-like structure?

0 votes

1 answer

796 views

                          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 the row above giving the value of b2  (1231342138adc2fehj3j21321321321kjk1423j32k9) as an input parameter.

Something like: 

    SELECT * 
    FROM table 
    WHERE [Column2 contains b which contains b2 which has value '1231342138adc2fehj3j21321321321kjk1423j32k9'] ; 

OR just:

    SELECT * 
    FROM table 
    WHERE [value '1231342138adc2fehj3j21321321321kjk1423j32k9' exists in row from Column2 ]


I just don't know how to give this instruction.

Any help is greatly appreciated.
                        

Asked by JustNatural (105 rep)

Sep 8, 2022, 02:18 PM
Last activity: Sep 8, 2022, 04:31 PM

How do I query inside a JSONB column when all values have the same json-like structure?

Related Questions