Could you please provide insight on how to extract data from a varchar type in both Presto and Hive?
[{u'pageId': u'102', u'title': u'ud648', u'isBrandHomePage': 1, u'active': True, u'pageType': 1, u'type': u'page'}, {u'pageId': u'103', u'title': u'uc804uccb4 uc0c1ud488', u'isBrandHomePage': 0, u'active': True, u'pageType': 2, u'type': u'page'}, {u'pageId': u'104', u'title': u'uae30ud68duc804', u'isBrandHomePage': 0, u'active': True, u'pageType': 0, u'type': u'page'}]
Converting JSON in Hive: transforming JSON into valid format by removing brackets, splitting into individual records, and using json_tuple:
with mytable as (
select "[{u'pageId': u'102', u'title': u'ud648', u'isBrandHomePage': 1, u'active': True, u'pageType': 1, u'type': u'page'}, {u'pageId': u'103', u'title': u'uc804uccb4 uc0c1ud488', u'isBrandHomePage': 0, u'active': True, u'pageType': 2, u'type': u'page'}, {u'pageId': u'104', u'title': u'uae30ud68duc804', u'isBrandHomePage': 0, u'active': True, u'pageType': 0, u'type': u'page'}]" json
)
select pageId,title,isBrandHomePage,active,pageType,type
from
(
select t.json,
regexp_replace(
regexp_replace(
regexp_replace(
regexp_replace(t.json,'\\[|\\]',''),
"u\\x27|\\x27",'"'),
'True','true'),
'False','false') json_fixed
from mytable t
)s
lateral view outer explode(split(json_fixed,'(?<=\\}), *(?=\\{)')) e as str_struct
lateral view json_tuple(e.str_struct,'pageId','title','isBrandHomePage','active','pageType','type') x as pageId,title,isBrandHomePage,active,pageType,type
Final Outcome:
Page ID Title Is Brand Home Page Active Page Type Type
102 ud648 1 true 1 page
103 uc804uccb4 uc0c1ud488 0 true 2 page
104 uae30ud68duc804 0 true 0 page
Syntax Parsing in Presto:
with mytable as (
select '[{u''pageId'': u''102'', u''title'': u''ud648'', u''isBrandHomePage'': 1, u''active'': True, u''pageType'': 1, u''type'': u''page''}, {u''pageId'': u''103'', u''title'': u''uc804uccb4 uc0c1ud488'', u''isBrandHomePage'': 0, u''active'': True, u''pageType'': 2, u''type'': u''page''}, {u''pageId'': u''104'', u''title'': u''uae30ud68duc804'', u''isBrandHomePage'': 0, u''active'': True, u''pageType'': 0, u''type'': u''page''}]' json
)
select json_extract(record,'$.pageId') pageId,
json_extract(record,'$.title') title,
json_extract(record,'$.isBrandHomePage') isBrandHomePage,
json_extract(record,'$.active') active,
json_extract(record,'$.pageType') pageType,
json_extract(record,'$.type') type
from
(
select t.json,
regexp_replace(
regexp_replace(
regexp_replace(t.json,'u\x27|\x27','"'),
'True','true'),
'False','false') json_fixed
from mytable t
)s
CROSS JOIN
UNNEST( CAST(JSON_PARSE(json_fixed) AS ARRAY<JSON>) ) as x(record)
Final Outcome:
Page Id Title Is Brand Home Page Active Page Type Type
"102" "ud648" 1 TRUE 1 "page"
"103" "uc804uccb4 uc0c1ud488" 0 TRUE 2 "page"
"104" "uae30ud68duc804" 0 TRUE 0 "page"
I'm facing a challenge with mapping and displaying a JSON file in a different structure. I need help figuring out how to map the data properly. Here's the JSON file: var data = { "megamenu": [ { "name": "level1.2", "link": "#", ...
Just wanted to mention that I am still learning about class based components, setState, and other concepts in async JS like axios. Below is a very basic example of what I can currently do. This is App.js: import Questions from './components/Ques ...
Is there a way to persist the selection stored in state even after page navigation? I have heard that using local storage is a possible solution, which is my preferred method. However, I have only found resources for implementing this in functional compone ...
It's past 2am and I find myself going crazy trying to identify an error. The console keeps repeating the message: "Error: webpack.optimize.UglifyJsPlugin has been removed, please use config.optimization.minimize instead." I've attempted modifyi ...
I am facing an issue with my LottieFiles (JSON) displaying a black background when imported into my NextJS project. Despite trying to set background='transparent' on both the Player and parent div, the problem persists. I made sure to export my ...
I have a dataset with JSON data containing ISO dates, and my goal is to filter out entries based on the "date_created" field falling within a specific date range. The time component should be ignored in this comparison, and the original JSON data should re ...
I am working with an element that looks like this: const DropdownElements = [ { key: 1, title: "City", placeholder: "Select City", apiUrl: "https://api.npoint.io/995de746afde6410e3bd", type: "city&qu ...
I am currently facing a challenge in rendering a React component that includes data fetched from a JSON using the fetch() method. Although the API call is successful, I am experiencing difficulties in displaying the retrieved data. Below is the code snip ...
I am facing an issue with my handleChange function. While I can easily change data in the same directory without any problem, I'm struggling to update sub-data. I would like to find a clean solution for this without having to add extra functions withi ...
I have successfully retrieved data using this API, but now I need to transform it into a chart within my react project. As a newcomer to JS and React, I am struggling to create a chart with the JSON data. My objective is to display prices by bedrooms over ...
My data structure functions correctly when I use console.log: console.log(JSON.parse([values.category2]).needed_skills); However, when I pass the output of JSON.parse([values.category2]).needed_skills to a component in my application, the entire system c ...
Having a background in mobile development, I am relatively new to web development so please excuse any amateur questions. Currently, I am working on a react.js project using create-react-app (which utilizes Babel). I am following a tutorial that requires ...
As a beginner in React, I am struggling with the concepts of importing, exporting, and rendering components. I have a fakeData.json file within the same src/components folder as the component I want to render. Let's take a look at the structure: < ...
I'm encountering some issues while trying to deploy my Github Pages website at . The errors I keep facing are as follows: - Failed to load resource: the server responded with a status of 400 () - manifest.json:1 Failed to load resource: the server ...
How can I extract the value '00000000000000000000000000000000' that comes after clusters in the links->href in a Rest Api response? [{ "analysisUnits": [ { "links": [ { "href": "http://127.0. ...
Using the fetch method in various components: fetch(url) .then(result => { if (!result.ok) { throw new Error("HTTP error " + result.status) } return result.json() }) .then(result => { ...
Currently, I am in the process of developing a CSV file loader that converts data into JSON format. The loader will consist of three buttons: 1. The first button allows me to select a file from my computer and load the CSV data for conversion to JSON. 2. ...
I'm new to working with JSON arrays and I'm facing a challenge. I am looking for a way to load data from a JSON file into a Typescript variable so that I can perform a specific operation that involves arrays. However, I'm unsure of how to ac ...
When working on my app, I faced the task of importing a JSON file and displaying its contents in a table with 3 columns. However, there are two main issues that arose: Utilizing array index for the row key is not recommended due to the table also having ...
I've been trying to getStaticPaths and getStaticProps to work, but I feel like I might be overlooking something. I attempted querying inside each of them, which seems redundant, but I'm not sure how else to do it. Can someone provide an example? ...