I have 2 string columns (thousands of rows) with ordered numbers in each string (there can be zero to ten numbers in each string). Example: The end result is to combine these 2 columns, sort the numbers in ascending order and then put each number into individual columns (smallest, 2nd smallest etc). e.g. Cols…
Tag: json
Get array size in oracle sql
I have a table in oracle with this definition Document is stored in a json format. How can i extract size for array parameters in sql? I need extract array size for each table record, and after sum all this values. ex: 1 step: 2 step: Can someone please give me an idea how to do that ? Answer Provided
how to find the position of string search in a json array column in laravel
i have a query which returns boolean value from a json array column about whether the string i am searching exists or not the column is something liek this —— [“a”,”b”,”c”] and the query is below but i want to get the position where the string was found… l…
‘cannot extract element from a scalar’ when order by id is DESC
I have a PostgreSQL 9.3.10 database, a table with 15842 records and use Intellij for making queries. I want to query some recent data from JSON column (named ‘data’). My query is that simple: The problem is that I get: [22023] ERROR: cannot extract element from a scalar when I use desc order. When…
Optimizing GROUP BY + COUNT DISTINCT on unnested jsonb column
I am trying to optimize a query in Postgres, without success. Here is my table: I have indexes on id and meta columns: There is 62k rows in this table. The request I’m trying to optimize is this one: In this query, meta is a dict like this one: I want to get the full list of key / value
How to format query result to show result in a specific json output
I am trying to learn how to format my result from SQL query to have my output as JSON. I will really appreciate if someone could guide as I will generate my results in different types of charts. For instance, one chart need to have the following JSON structure: and I am using Northwind stored procedure With t…
Convert multiple SQL columns in a row to JSON and update to a column in the same row
I’m trying to serialize multiple columns in a row to JSON and update it to a column in the same row: UPDATE dbo.ItemSpecification SET ItemCameraData = (SELECT CameraFocalLengthMin, …
How do I preserve utf-8 JSON values and write them correctly to a utf-8 txt file in Python 3.8.2?
I recently wrote a python script to extract some data from a JSON file and use it to generate some SQL Insert values for the following statement: The names of some of the attributes are different in my SQL table but the same values are used (example cardClass in the JSON file/Python script is referred to as c…
Importing JSON data from SQL DB to an R dataframe
I would like to know whether there is a way of importing JSON data from a MySQL DB to an R dataframe. I have a table like this: I would like to get the columns ‘id’ and ‘json’. I am using RMySQL package for getting the data from the db to an R dataframe but this gives me only the
Combine different CTE json objects together
I am having difficulties grasping the concept of combining two CTE json objects together. The data from the two are matched by a field WOID, which is the “workOrderID” in this json. I do not know …