# Cannot use more than one JSON field in query **URL:** \ **Category:** Uncategorized\ **Created:** [January 17, 2019, 7:53pm UTC](https://community.dremio.com/t/cannot-use-more-than-one-json-field-in-query/2572 "2019-01-17T19:53:46Z")\ **Posts on this page:** 14\ **Page:** 1
**Author:** ![jhaynie](https://sea2.discourse-cdn.com/flex020/user_avatar/community.dremio.com/jhaynie/32/1131_2.png) [@jhaynie](https://community.dremio.com/u/jhaynie)\ **Post date:** [January 17, 2019, 7:53pm UTC](https://community.dremio.com/t/cannot-use-more-than-one-json-field-in-query/2572/1 "2019-01-17T19:53:46Z")
We have a string column called metadata which is JSON. ``` SELECT t."metadata".team_id, t."metadata".team_name FROM ( SELECT s.customer_id, s."value" AS value_current, s."value" AS value_prior, s."value" AS value_change, convert_from(s."metadata", 'JSON') AS "metadata" FROM dev.backend.entity."signal".v1."signal" s WHERE s.name = 'PerformanceTeam' AND s.date_ts = (SELECT MAX(date_ts) FROM dev.backend.entity."signal".v1."signal" WHERE customer_id = s.customer_id) AND s.ref_type = 'team' ) t ``` This query returns an error if we use more than one field in the JSON. Any of the fields we use are fine if we only use one. If we use more than one, it fails. Funny enough, this work around which is horrible works: ``` SELECT t.customer_id, t2."metadata".team_id AS team_id, t2."metadata".team_name AS team_name, t.value_current, t.value_prior, t.value_change FROM ( SELECT id, s.customer_id, s."value" AS value_current, s."value" AS value_prior, s."value" AS value_change FROM dev.backend.entity."signal".v1."signal" s WHERE s.name = 'PerformanceTeam' AND s.date_ts = (SELECT MAX(date_ts) FROM dev.backend.entity."signal".v1."signal" WHERE customer_id = s.customer_id) AND s.ref_type = 'team' ) t, ( SELECT id, convert_from(s."metadata", 'JSON') AS "metadata" FROM dev.backend.entity."signal".v1."signal" s WHERE s.name = 'PerformanceTeam' AND s.date_ts = (SELECT MAX(date_ts) FROM dev.backend.entity."signal".v1."signal" WHERE customer_id = s.customer_id) AND s.ref_type = 'team' ) t2 WHERE t.id = t2.id ``` Here’s the profile for the first query. [f38161e5-09d8-4c2d-828d-b39e12b0f5da.zip](https://us1.discourse-cdn.com/flex020/uploads/dremio/original/2X/c/c3f30541d8b5b96c980d658263dbffa633c3b75d.zip) (5.6 KB) ---
**Author:** ![balaji.ramaswamy](https://sea2.discourse-cdn.com/flex020/user_avatar/community.dremio.com/balaji.ramaswamy/32/843_2.png) [@balaji.ramaswamy](https://community.dremio.com/u/balaji.ramaswamy)\ **Post date:** [January 17, 2019, 8:23pm UTC](https://community.dremio.com/t/cannot-use-more-than-one-json-field-in-query/2572/2 "2019-01-17T20:23:57Z")
Hi @jhaynie What type is “metadata”, is it a complex object? Thanks @balaji.ramaswamy ---
**Author:** ![jhaynie](https://sea2.discourse-cdn.com/flex020/user_avatar/community.dremio.com/jhaynie/32/1131_2.png) [@jhaynie](https://community.dremio.com/u/jhaynie)\ **Post date:** [January 17, 2019, 8:36pm UTC](https://community.dremio.com/t/cannot-use-more-than-one-json-field-in-query/2572/3 "2019-01-17T20:36:30Z")
Yes, its stored as a STRING field in parquet. But the string value is a JSON object with multiple fields. It looks something like this: `{"active":true, "team_id":"4fa4a5e4578444b5", "team_name":"some team name too", "team_count":7, "team_salary":300000, "cost_percentile":0.333333333333333, "signals":[{"name":"CycleTime","value":68},{"name":"Throughput","value":8},{"name":"OnTimeDelivery","value":78}]}` ---
**Author:** ![balaji.ramaswamy](https://sea2.discourse-cdn.com/flex020/user_avatar/community.dremio.com/balaji.ramaswamy/32/843_2.png) [@balaji.ramaswamy](https://community.dremio.com/u/balaji.ramaswamy)\ **Post date:** [January 17, 2019, 8:47pm UTC](https://community.dremio.com/t/cannot-use-more-than-one-json-field-in-query/2572/4 "2019-01-17T20:47:16Z")
Hi @jhaynie Can you please send us "parquet-tools meta for this Parquet file? [Download, Install Parquet Tools](https://github.com/apache/parquet-mr/tree/master/parquet-tools) Thanks @balaji.ramaswamy ---
**Author:** ![jhaynie](https://sea2.discourse-cdn.com/flex020/user_avatar/community.dremio.com/jhaynie/32/1131_2.png) [@jhaynie](https://community.dremio.com/u/jhaynie)\ **Post date:** [January 17, 2019, 8:59pm UTC](https://community.dremio.com/t/cannot-use-more-than-one-json-field-in-query/2572/5 "2019-01-17T20:59:38Z")
Here’s a dump of one of the parquet files for the query [out.zip](https://us1.discourse-cdn.com/flex020/uploads/dremio/original/2X/1/113fa37f953bfa80d8f9e607a4fcc7ba7d683afd.zip) (1.1 KB) ---
**Author:** ![balaji.ramaswamy](https://sea2.discourse-cdn.com/flex020/user_avatar/community.dremio.com/balaji.ramaswamy/32/843_2.png) [@balaji.ramaswamy](https://community.dremio.com/u/balaji.ramaswamy)\ **Post date:** [January 17, 2019, 10:19pm UTC](https://community.dremio.com/t/cannot-use-more-than-one-json-field-in-query/2572/6 "2019-01-17T22:19:10Z")
Thanks @jhaynie Was able to reproduce the issue. Let me get bak to you on what is going on here Thanks @balaji.ramaswamy ---
**Author:** ![aakash](https://avatars.discourse-cdn.com/v4/letter/a/9fc348/32.png) [@aakash](https://community.dremio.com/u/aakash)\ **Post date:** [January 15, 2021, 3:28pm UTC](https://community.dremio.com/t/cannot-use-more-than-one-json-field-in-query/2572/7 "2021-01-15T15:28:54Z")
Hi @balaji.ramaswamy We’re facing similar issues while trying to extract multiple fields from the json column extracted from parquet. Also, dremio doesn’t seem to identify the nested json column by itself. Could you share what the issue might be and any potential solution? ---
**Author:** ![balaji.ramaswamy](https://sea2.discourse-cdn.com/flex020/user_avatar/community.dremio.com/balaji.ramaswamy/32/843_2.png) [@balaji.ramaswamy](https://community.dremio.com/u/balaji.ramaswamy)\ **Post date:** [January 16, 2021, 6:39am UTC](https://community.dremio.com/t/cannot-use-more-than-one-json-field-in-query/2572/8 "2021-01-16T06:39:33Z")
@aakash If you have a JSON struct from a text file, Dremio might mark it as a string. All you need to do is "SELECT CONVERT\_FROMJSON(column\_name) as col\_name Thanks Bali ---
**Author:** ![aakashsingh](https://avatars.discourse-cdn.com/v4/letter/a/8e7dd6/32.png) [@aakashsingh](https://community.dremio.com/u/aakashsingh)\ **Post date:** [January 18, 2021, 2:01pm UTC](https://community.dremio.com/t/cannot-use-more-than-one-json-field-in-query/2572/9 "2021-01-18T14:01:56Z")
Thanks @balaji.ramaswamy . That seems to work for queries such as below: select nested\_0.blob1.col1 as col1 from (SELECT CONVERT\_FROMJSON(blob1) as blob1 from “test.parquet”) nested\_0 However, when we try to query more than one columns from the nested data structure such as below, we get the error: **Using CONVERT\_FROM(\*, ‘JSON’) is only supported against string literals and direct table references of types VARCHAR and VARBINARY.** select nested\_0.blob1.col1 as col1, nested\_0.blob1.col1 as col2 from (SELECT CONVERT\_FROMJSON(blob1) as blob1 from “test.parquet”) nested\_0 Is there a way to achieve querying multiple columns? ---
**Author:** ![balaji.ramaswamy](https://sea2.discourse-cdn.com/flex020/user_avatar/community.dremio.com/balaji.ramaswamy/32/843_2.png) [@balaji.ramaswamy](https://community.dremio.com/u/balaji.ramaswamy)\ **Post date:** [January 18, 2021, 9:59pm UTC](https://community.dremio.com/t/cannot-use-more-than-one-json-field-in-query/2572/10 "2021-01-18T21:59:13Z")
@aakashsingh Try this, using the UI, do the extract option and see what SQL is generated. Do you see the struct symbol for the column datatype? Or does it still show “Abc”? ---
**Author:** ![nld-8832](https://sea2.discourse-cdn.com/flex020/user_avatar/community.dremio.com/nld-8832/32/3858_2.png) [@nld-8832](https://community.dremio.com/u/nld-8832)\ **Post date:** [January 14, 2022, 5:18am UTC](https://community.dremio.com/t/cannot-use-more-than-one-json-field-in-query/2572/11 "2022-01-14T05:18:47Z")
Hi, am running into the same issue. My JSON string looks like OP too format-wise, and fyi I do see the struct symbol in the column. ---
**Author:** ![balaji.ramaswamy](https://sea2.discourse-cdn.com/flex020/user_avatar/community.dremio.com/balaji.ramaswamy/32/843_2.png) [@balaji.ramaswamy](https://community.dremio.com/u/balaji.ramaswamy)\ **Post date:** [January 20, 2022, 6:39am UTC](https://community.dremio.com/t/cannot-use-more-than-one-json-field-in-query/2572/12 "2022-01-20T06:39:23Z")
@nld-8832 You have to give a column name, what is your CONVERT\_FROMJSON command? ---
**Author:** ![priyanka](https://sea2.discourse-cdn.com/flex020/user_avatar/community.dremio.com/priyanka/32/3349_2.png) [@priyanka](https://community.dremio.com/u/priyanka)\ **Post date:** [February 4, 2022, 7:43pm UTC](https://community.dremio.com/t/cannot-use-more-than-one-json-field-in-query/2572/13 "2022-02-04T19:43:18Z")
Is there any other workaround for this issue? I am seeing the exact issue but unlike the OP I don’t have any other fields/unique columns that can help me write a join condition to separate the JSON and non-JSON fields in 2 separate t. @balaji.ramaswamy ---
**Author:** ![balaji.ramaswamy](https://sea2.discourse-cdn.com/flex020/user_avatar/community.dremio.com/balaji.ramaswamy/32/843_2.png) [@balaji.ramaswamy](https://community.dremio.com/u/balaji.ramaswamy)\ **Post date:** [February 5, 2022, 4:05pm UTC](https://community.dremio.com/t/cannot-use-more-than-one-json-field-in-query/2572/14 "2022-02-05T16:05:41Z")
@priyanka Do you have one list/array or struct that has more structs inside, can you share a sample format with dummy data?