Is there a better way to write this Postgres JSONB query?

Question

Below is a sample JSONB array. I'm trying to figure out how to write a query that doesn't require a cross product like this.

select b.id from brand b,jsonb_array_elements (b.tree) a where a#>>'{Name}'  = 'Skiing';

Bonus points for helping me translate this to SQL Alchemy

[
  {
    "Name": "Snowboarding",
    "Order": 1,
    "Categories": {
      "Jackets": [
        22002,
        23224
      ],
      "Helmets": [
        24920
      ],
      "Freestyle Boards": [
        20164
      ],
      "Goggles": [
        23169,
        23280
      ],
      "Hats": [
        22966,
        21727
      ],
      "Bindings": [
        19265
      ],
      "Gloves": [
        20461
      ],
      "Boots": [
        26374,
        19079,
        21765,
        22669
      ],
      "Freeride Boards": [
        18395,
        25505
      ],
      "Pants": [
        24143,
        20957
      ]
    }
  },
  {
    "Name": "Skiing",
    "Order": 2,
    "Categories": {
      "Jackets": [
        22518,
        25791,
        19972
      ],
      "Pants": [
        17516,
        23113
      ],
      "Goggles": [
        25066,
        20996
      ],
      "Helmets": [
        24378
      ],
      "Hats": [
        20009,
        21245
      ],
      "Cross-country Skiing": [
        17464
      ],
      "Gloves": [
        25822
      ],
      "Boots": [
        16616
      ],
      "Poles": [
        19280
      ]
    }
  },....]

van · Accepted Answer · 2016-01-05 19:31:18Z

1

SQL solution first:

SELECT  brand.id
FROM    brand
WHERE   brand.tree @> '[{"Name": "Skiing"}]'::jsonb;

As for sqlalchemy version, you can simply use contains in order to generate SQL statement above:

q = (session.query(Brand.id)
     .filter(Brand.tree.contains([{"Name": "Skiing"}]))
     )

answered Jan 5, 2016 at 19:31

van

77.6k13 gold badges174 silver badges179 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

Trent Over a year ago

sweet, i was missing the outer brackets

Collectives™ on Stack Overflow

Is there a better way to write this Postgres JSONB query?

1 Answer 1

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related