How to query nested JSONB postgres in Rails-CodePudding

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?

CodePudding user response：

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"');

CodePudding user response：

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