Shopify Question posted in
The Shopify help center can be found here.

JSON Parsing in Snowflake – Square Brackets At Start – Shopify

I’m trying to parse out some JSON files in snowflake. In this case, I’d like to extract the "gift card" from the line that has "fulfillment_service": "gift_card". I’ve had success querying one dimensional JSON data, but this – with the square brackets – is confounding me.

Here’s my simple query – I’ve created a small table called "TEST_WEEK"

select line_items:fulfillment_service
from TEST_WEEK
, lateral flatten(FULFILLMENTS:line_items) line_items;

Hopefully this isn’t too basic a question. I’m very new with parsing JSON.

Thanks in advance!

Here’s the start of the FULLFILLMENTS field with the info I want to get at.

[
  {
    "admin_graphql_api_id": "gid://shopify/Fulfillment/2191015870515",
    "created_at": "2020-08-10T14:54:38Z",
    "id": 2191015870515,
    "line_items": [
      {
        "admin_graphql_api_id": "gid://shopify/LineItem/5050604355635",
        "discount_allocations": [],
        "fulfillable_quantity": 0,
        "fulfillment_service": "gift_card",
        "fulfillment_status": "fulfilled",
        "gift_card": true,
        "grams": 0,
        "id": 5050604355635,
        "name": "Gift Card - $100.00",
        "origin_location": {
          "address1": "100 Indian Road",
          "address2": "",
          "city": "Toronto",
          "country_code": "CA",
Tags:

3

Answers


  1. 0

    Those square brackets indicate that you have an array of JSON objects in your FULLFILLMENTS field. Unless there is a real need to have an array of objects in one field you should have a look at the STRIP_OUTER_ARRAY property of the COPY command. An example can be found here in the Snowflake documentation:

    copy into <table>
from @~/<file>.json
file_format = (type = 'JSON' strip_outer_array = true);
    Login or Signup to reply.
  2. 0

    Maybe you can use two lateral flatten to process values in line_items array:

    Sample table:

    create table TEST_WEEK( FULFILLMENTS variant ) as
select parse_json(
'[
  {
    "admin_graphql_api_id": "gid://shopify/Fulfillment/2191015870515",
    "created_at": "2020-08-10T14:54:38Z",
    "id": 2191015870515,
    "line_items": [
      {
        "admin_graphql_api_id": "gid://shopify/LineItem/5050604355635",
        "discount_allocations": [],
        "fulfillable_quantity": 0,
        "fulfillment_service": "gift_card",
        "fulfillment_status": "fulfilled",
        "gift_card": true,
        "grams": 0,
        "id": 5050604355635,
        "name": "Gift Card - $100.00",
        "origin_location": {
          "address1": "100 Indian Road",
          "address2": "",
          "city": "Toronto",
          "country_code": "CA"
      }
      }
      ]
  }
]');

    Sample query:

    select s.VALUE:fulfillment_service 
from TEST_WEEK, 
lateral flatten( FULFILLMENTS ) f,
lateral flatten( f.VALUE:line_items ) s;

    The output:

    +-----------------------------+
| S.VALUE:FULFILLMENT_SERVICE |
+-----------------------------+
| "gift_card"                 |
+-----------------------------+
    Login or Signup to reply.
  3. 0

    In case others are stuck with same data issue (all json data in one array), I have this solution:

    select f.VALUE:fulfillment_service::string
from TEST_WEEK, 
lateral flatten( FULFILLMENTS[0].line_items ) f;

    With this, you just grab the first element of the array (which is the only element).

    If you have nested array elements, just add this to the lateral flatten:

    , RECURSIVE => TRUE, mode => 'array'
    Login or Signup to reply.
Please signup or login to give your own answer.

Back To Top
Search