Select query for jsonb column. Alternative for 'like'-CodePudding

I have a postrgresql table with jsonb jsonb_data column:

    | jsonb_data                                          |
    | --------------------------------------------------- |
    | [ {"a": {"aa": "", "ab": 0}, "b": null, "c": ""} ]  |
    | [ {"a": {"aa": ""}, "b": {"ba": "", "bb": 0} ]      |
    | [ "c": {"ca": 1} ]                                  |
    | [ "b": {"bb": 0} ]                                  |

How Can I select all rows, where exist "b" ? This query must return such rows:

    | jsonb_data                                          |
    | --------------------------------------------------- |
    | [ {"a": {"aa": "", "ab": 0}, "b": null, "c": ""} ]  |
    | [ {"a": {"aa": ""}, "b": {"ba": "", "bb": 0} ]      |
    | [ "b": {"bb": 0} ]                                  |

CodePudding user response：

CREATE temp TABLE test_like (
    data jsonb
);

INSERT INTO test_like
    VALUES ('[{"a": {"aa": "", "ab": 0}}, {"b": null, "c": ""} ] '),
    ('[ {"a": {"aa": ""}}, {"b": {"ba": "", "bb": 0}}]'),
    ('[ {"c": {"ca": 1}} ]'),
    ('[ {"b": {"bb": 0}} ]');

WITH cte AS (
    SELECT
        data,
        jsonb_path_exists(data, '$[*].b')
    FROM
        test_like
)
SELECT
    data
FROM
    cte
WHERE
    jsonb_path_exists;