Question 108940 in Json Question posted in
Our archive of expertly curated questions and answers provides insights and solutions to common problems related to this popular data interchange format. From parsing and manipulating JSON data to integrating it with various programming languages and web services, our archive has got you covered. Start exploring today and take your JSON skills to the next level

How to determine if a json field exists within an array in presto?

If I have a field jsonCol which has list of json objects, for example:

[{'name': 'fieldA', 'enum': 'someValA'},
 {'name': 'fieldB', 'enum': 'someValB'},
 {'name': 'fieldC', 'enum': 'someValC'}]

Another row may look like:

[{'name': 'fieldA', 'enum': 'someValA'},
 {'name': 'fieldC', 'enum': 'someValC'}]

How do I get rows where the fieldB exists?
I have a query that can look for the value of fieldB, howver the query fails in cases when fieldB doesn’t exist with the error:

Error running query: Array subscript must be less than or equal to array length: 1 > 0

My query:

SELECT
    json_extract_scalar(filter(cast(json_parse(jsonCol) AS array(json)), x -> json_extract_scalar(x, '$.name') = 'fieldB')[1], '$.enum') AS myField
FROM myTable
WHERE
    json_extract_scalar(filter(cast(json_parse(jsonCol) AS array(json)), x -> json_extract_scalar(x, '$.name') = 'fieldB')[1], '$.enum') = 'someValB'

How can I check for the value of someValB but also ignore cases when the json doesn’t exist at all?

Tags:

2

Answers


  1. 0

    This is a working solution for SQLite.

    json_each() used to returns row for each array element or object member.

    json_extract() extracts and returns one or more values from json object.

    with cte as (
  select key, value, json_extract(value,'$.name') as name, json_extract(value,'$.enum') as enum
  from myTable, json_each(jsonCol)
)
select enum
from cte
where name = 'fieldB';

    Result :

    enum
———————-
someValB

    Demo here

    Login or Signup to reply.
  2. 0

    Use element_at instead of array access with [1]:

    -- sample data
with dataset (jsonCol) as (
    values ('[{"name": "fieldA", "enum": "someValA"},
 {"name": "fieldB", "enum": "someValB"},
 {"name": "fieldC", "enum": "someValC"}]'),
        ('[{"name": "fieldA", "enum": "someValA"},
 {"name": "fieldC", "enum": "someValC"}]')
)

-- query
SELECT
    json_extract_scalar(
        element_at(
            filter(cast(json_parse(jsonCol) AS array(json)), x -> json_extract_scalar(x, '$.name') = 'fieldB'),
            1)
        , '$.enum') AS myField
FROM dataset
WHERE
    json_extract_scalar(
        element_at(
            filter(cast(json_parse(jsonCol) AS array(json)), x -> json_extract_scalar(x, '$.name') = 'fieldB'),
            1)
        , '$.enum') = 'someValB';

    Output:

     myField
----------
 someValB

    Note that possibly you can simplify the query by moving some parts into WITH clause or subquery.

    If you can switch to Trino – you can use it’s improved json path support via json_query (or json_exists). TBH I don’t fully understand your goal but here are some options:

    -- query
SELECT
    JSON_QUERY(jsonCol, 'lax $[*]?(@.name == "fieldB" && @.enum == "someValB")[0].enum') myField,
    JSON_QUERY(jsonCol, 'lax $[*]?(@.name == "fieldB").enum' WITH ARRAY WRAPPER) myFieldArr
FROM dataset;

    Output:

      myField   |  myFieldArr
------------+--------------
 "someValB" | ["someValB"]
 NULL       | NULL
    Login or Signup to reply.
Please signup or login to give your own answer.

Back To Top
Search