How to query nested JSONB postgres in Rails

Question

I've a table called Order with a jsonb column type called line_items. The line_items column can contain nested values like this:

[
  {
    "id":9994857545813,
    "sku":"CLIPPING-PATH_C2_24H",
  },
  {
    "id":9994857578581,
    "sku":"NATURAL-SHADOW_C1_24H",
  }
]

The above example has two line items in it but it can vary from 1 to any number of line items.

I need to query all orders that contains only 1 line item where sku = a particular value such as CLIPPING-PATH_C2_24H as per above example.

So, the query should not match the above example but the following that has only 1 line item and sku=CLIPPING-PATH_C2_24H

[
  {
    "id":9994857545813,
    "sku":"CLIPPING-PATH_C2_24H",
  }
]

Can any help to write the query using Rails active record?

Use the JSON containment operator @>. Don't ever store tabular data as JSON! — Laurenz Albe
– Laurenz Albe, Commented May 24, 2022 at 6:07

Asad Shakil · Accepted Answer · 2022-05-25 08:13:33Z

1

I was able to figure this out using the jsonb_array_length method:

Order
.where("line_items @> ?", [{sku: sku}].to_json)
.where("jsonb_array_length(line_items) = 1")
.count
end

The following resource was extremely helpful: https://gist.github.com/mankind/1802dbb64fc24be33d434d593afd6221

answered May 25, 2022 at 8:13

Asad Shakil

1332 silver badges11 bronze badges

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

Comments

jian · Accepted Answer · 2022-05-24 07:47:12Z

demo
You can call plpghsql in ruby.(How to call plpgsql functions from Ruby on rails?)

sql query: select jsonb_path_query(order_json,'$[*] ? (@.sku == "CLIPPING-PATH_C2_24H")') from orders ;

it's not easy to get it right, since some part of PL/pgsql function string even include 4 single quotes. Better use raise notice to test it step by step.

CREATE OR REPLACE FUNCTION get_sku_CLIPPING_path (_sku text)
    RETURNS json
    AS $$
DECLARE
    _sql text;
    _returnjson jsonb;
BEGIN
    RAISE NOTICE '%: _sku', $1;
    RAISE NOTICE '%', '$[*] ? (@.sku == ' || $1 || ')';
    RAISE NOTICE '%', $s$
    SELECT
        jsonb_path_query(order_json, $s$ || '''' || '$[*] ? (@.sku == ' || $1 || ')''' || ' from orders';
    _sql := $s$
    SELECT
        jsonb_path_query(order_json, $s$ || '''' || '$[*] ? (@.sku == ' || $1 || ')''' || ' )from orders';
    EXECUTE _sql
    USING _sku INTO _returnjson;
    RETURN (_returnjson::json);
END
$$
LANGUAGE plpgsql;

call it: select * from get_sku_CLIPPING_path('"CLIPPING-PATH_C2_24H"');

Sahap Asci · Accepted Answer · 2022-05-24 12:56:11Z

First things first, you don't have a nested json. You just have a json array with objects. Moreover your json objects can be representable as tabular. It's better to store those objects in another table and setup a one to many relationship. You can have an "orders" table and an "order_details" table.

To get data you need, first we need to find records which have "sku":"CLIPPING-PATH_C2_24H" then we need to parse json and get that object from line_items fields.

SELECT
    t.*
  FROM orders o,
  -- extract object from array of json objects 
  LATERAL jsonb_path_query(o.line_items, '$[*] ? (@.sku == $value)', '{"value" : "CLIPPING-PATH_C2_24H"}') order_line,
  -- convert "sku":"NATURAL-SHADOW_C1_24H" into columns 
  LATERAL jsonb_to_record(order_line) as t(id bigint, sku text)
  WHERE
    -- find record which has "sku":"NATURAL-SHADOW_C1_24H"
    o.line_items @> '[{"sku":"NATURAL-SHADOW_C1_24H"}]';

Result will be like this;

           id | sku                 
------------- | --------------------
9994857545813 | CLIPPING-PATH_C2_24H

fiddle is here

Collectives™ on Stack Overflow

How to query nested JSONB postgres in Rails

3 Answers 3

Comments

Comments

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

Comments

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related