question Re: Spark SQL JSON array querry ? in Archives of Support Questions (Read Only)

Spark SQL JSON array querry ?

MabuXayda — Fri, 16 Sep 2022 09:26:09 GMT

I have a simple JSON dataset as below. How do I query all parts.lock

JSON:

{
    "id": 1,
    "name": "A green door",
    "price": 12.50,
    "tags": ["home", "green"],
    "parts" : [
        {
            "lock" : "One lock",
            "key" : "single key"
        },
        {
            "lock" : "2 lock",
            "key" : "2 key"
        }
    ]
}

Query:

select id,name,price,parts.lock from product

The point is if I use parts[0].lock it will return one row as below:

{u'price': 12.5, u'id': 1, u'.lock': {u'lock': u'One lock', u'key': u'single key'}, u'name': u'A green door'}

But I want to return all the locks in the parts structure.

Please help me with this, thanks !

Re: Spark SQL JSON array querry ?

chrisf — Wed, 08 Apr 2015 23:39:26 GMT

you're looking for LATERAL VIEW EXPLODE:

http://apache-spark-user-list.1001560.n3.nabble.com/flattening-a-list-in-spark-sql-td13300.html

Re: Spark SQL JSON array querry ?

MabuXayda — Wed, 15 Apr 2015 09:54:13 GMT

Thanks for your reply chrisf,

I've been trying to use LATERAL VIEW explode for week but still can't figure how to use it, can you give me an example from my first post.

I also try json-serde in HiveContext, i can parse table, but can't querry although the querry work fine in Hive.

EX:

+ In both Hive anh HiveContext, i can parse table:

CREATE EXTERNAL TABLE data( parts array<struct<locks:STRING, key:STRING>> ) ROW FORMAT SERDE 'org.openx.data.jsonserde.JsonSerDe' LOCATION '/user/hue/...';

+ Then in Hive, i can use this:

SELECT parts.locks FROM data;

but it will return error in HiveContext.

Looking forward for reply, thanks !

Re: Spark SQL JSON array querry ?

MabuXayda — Thu, 16 Apr 2015 04:58:59 GMT

Oh, I finally do it, follow is my hql:

SELECT id, part.lock, part.key FROM mytable EXTERNAL VIEW explode(parts) parttable AS part;

many thanks chrisf !